Two ideas that hold a database together
A table is only useful if you can point at one particular row, and a database is only useful if tables can refer to each other safely. Primary keys do the first job and foreign keys do the second. After this lesson you will be able to choose a key for a table, link two tables, and say exactly what happens when someone deletes a row that others depend on.
The examples use new tables created in a scratch SQLite 3.54 database, plus the sample company database to show keys you have already been using.
Primary keys
A primary key is a column, or a group of columns, that uniquely identifies each row. It combines two rules you met in the previous lesson: it must be unique and it can never be NULL. A table may have only one primary key.
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name VARCHAR(60) NOT NULL
);
INSERT INTO authors (name) VALUES ('Leela Menon'), ('Jay Parekh');
SELECT * FROM authors;+----+-------------+
| id | name |
+----+-------------+
| 1 | Leela Menon |
| 2 | Jay Parekh |
+----+-------------+The inserts never mentioned id, yet the rows got 1 and 2. That is auto-numbering, which every database offers but spells differently:
- SQLite: a column declared exactly
INTEGER PRIMARY KEYfills itself in. TheAUTOINCREMENTkeyword is optional and only changes whether old numbers are ever reused. - MySQL:
INT PRIMARY KEY AUTO_INCREMENT. - PostgreSQL:
INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, or the olderSERIAL. - SQL Server:
INT IDENTITY(1,1) PRIMARY KEY.
This is one of the places where portable SQL runs out, and you simply have to learn the form for the database you use. SQLite does not support the GENERATED ... AS IDENTITY form, so the examples here use its own.
Duplicate keys are refused:
INSERT INTO authors (id, name) VALUES (1, 'Duplicate');Error: UNIQUE constraint failed: authors.idChoosing a key
Two styles exist. A surrogate key is a meaningless number made by the database, such as id. A natural key is a real-world value, such as an email address or a passport number. Beginners are usually better served by surrogate keys, because real-world values change, and a key that changes would have to be updated everywhere it is used. You can still protect the natural value with UNIQUE, as the sample employees table does with email.
Composite keys
Sometimes no single column identifies a row, but a pair does. In the sample database, assignments records which employee worked on which project. One employee may work on many projects, and one project has many employees, so neither column is unique alone, but the pair is. Its key is PRIMARY KEY (employee_id, project_id). Here is the same idea as a new table:
CREATE TABLE enrolments (
student_id INTEGER NOT NULL,
course_id INTEGER NOT NULL,
enrolled_on DATE NOT NULL,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO enrolments VALUES (1, 100, '2026-02-01'), (1, 101, '2026-02-01'), (2, 100, '2026-02-03');Student 1 appears twice and course 100 appears twice, and that is fine. Repeating a whole pair is not:
INSERT INTO enrolments VALUES (1, 100, '2026-03-01');Error: UNIQUE constraint failed: enrolments.student_id, enrolments.course_idForeign keys
A foreign key is a column whose values must exist in another table's key. It is how the database remembers a relationship. A book belongs to an author, so the book stores the author's id:
PRAGMA foreign_keys = ON;
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author_id INTEGER NOT NULL,
CONSTRAINT fk_books_author FOREIGN KEY (author_id) REFERENCES authors (id)
);
INSERT INTO books (title, author_id) VALUES ('Quiet Rivers', 1), ('Salt Roads', 1), ('Paper Moons', 2);The first line matters, and it is an SQLite peculiarity. SQLite reads foreign key definitions but, by default, does not enforce them. You must switch enforcement on with PRAGMA foreign_keys = ON; every time you open a connection. The sample database script does this at its top. In a plain SQLite session without the pragma, a book pointing at author 99 is happily accepted. MySQL (with the InnoDB engine), PostgreSQL and SQL Server enforce foreign keys without being asked.
With enforcement on, a book for a missing author is refused:
INSERT INTO books (title, author_id) VALUES ('Ghost Book', 99);Error: FOREIGN KEY constraint failedThe message is short, because SQLite does not say which key failed. Other databases name the constraint, which is another reason to name your constraints.
The parent side is protected too. Author 1 has books, so deleting that author would leave orphans, and it is refused:
DELETE FROM authors WHERE id = 1;Error: FOREIGN KEY constraint failedThe sample database behaves the same way. Department 1 has employees, so it cannot be deleted, and an assignment for employee 99, who does not exist, cannot be added. Department 6, Research, has no employees at all, so it can go.
A foreign key column may be NULL if the relationship is optional. Employee 18 in the sample database has no department because their department_id is NULL, and that is allowed.
What happens on delete: ON DELETE
By default a delete that would orphan rows is rejected, which is the safest behaviour and the one you just saw. You can choose another with ON DELETE:
NO ACTIONorRESTRICT: reject the delete (the default).SET NULL: keep the children and clear their link. The column must allowNULL.CASCADE: delete the children as well.
Here are both of the last two on a small example:
PRAGMA foreign_keys = ON;
CREATE TABLE teams (id INTEGER PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE players (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
team_id INTEGER,
FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE SET NULL
);
CREATE TABLE notes (
id INTEGER PRIMARY KEY,
team_id INTEGER NOT NULL,
body VARCHAR(100) NOT NULL,
FOREIGN KEY (team_id) REFERENCES teams (id) ON DELETE CASCADE
);
INSERT INTO teams VALUES (1, 'Reds'), (2, 'Blues');
INSERT INTO players VALUES (1, 'Uma', 1), (2, 'Vik', 1), (3, 'Wes', 2);
INSERT INTO notes VALUES (1, 1, 'Won the cup'), (2, 2, 'New coach');
DELETE FROM teams WHERE id = 1;
SELECT * FROM players;
SELECT * FROM notes;+----+------+---------+
| id | name | team_id |
+----+------+---------+
| 1 | Uma | NULL |
| 2 | Vik | NULL |
| 3 | Wes | 2 |
+----+------+---------++----+---------+-----------+
| id | team_id | body |
+----+---------+-----------+
| 2 | 2 | New coach |
+----+---------+-----------+One delete had two outcomes. The players of the Reds survive, with no team. The Reds' note vanished, because notes belong to a team and had CASCADE. Use CASCADE for data that has no meaning without its parent, such as the lines of an order, and think twice before using it anywhere else. A cascade can remove a surprising number of rows with one innocent-looking statement.
The same keywords exist in MySQL, PostgreSQL and SQL Server, with minor differences in what they allow, so check your database's documentation when you use anything beyond the basics. The MySQL foreign keys lesson shows them on MySQL, and the MySQL primary keys lesson covers its auto-numbering.
Common mistakes
- Forgetting
PRAGMA foreign_keys = ONin SQLite. The table definition looks right, but nothing is enforced, so orphan rows creep in. Put the pragma at the top of your scripts. - Using a changeable value as the key. Email addresses, names and phone numbers change. Use an id, and make the real-world value
UNIQUE. - Joining on columns that are not keys. It works, but you may match several rows when you expected one.
- Reaching for
CASCADEby default. Decide per relationship, and prefer rejecting the delete when the child rows matter. - Making a foreign key column
NOT NULLwhen the link is optional. Then every row must have a parent.
Exercise
Create tables customers (integer id primary key, required name) and invoices (integer id primary key, a required customer_id that refers to customers, and a required amount). Turn on foreign key enforcement, add one customer and one invoice for them, then try to add an invoice for customer 7.
Show one possible answer
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(60) NOT NULL
);
CREATE TABLE invoices (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
amount INTEGER NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);
INSERT INTO customers (name) VALUES ('Harsh Trading');
INSERT INTO invoices (customer_id, amount) VALUES (1, 5000);
SELECT * FROM invoices;+----+-------------+--------+
| id | customer_id | amount |
+----+-------------+--------+
| 1 | 1 | 5000 |
+----+-------------+--------+INSERT INTO invoices (customer_id, amount) VALUES (7, 100);Error: FOREIGN KEY constraint failedKey points
- A primary key identifies each row. It is unique and never NULL, and a table has at most one, which may span several columns.
- A foreign key makes a column refer to a primary key in another table, so a row cannot point at something that does not exist.
- ON DELETE decides what happens to child rows when the parent goes: reject the delete, SET NULL, or CASCADE the delete.
- SQLite does not enforce foreign keys unless you run PRAGMA foreign_keys = ON on each connection. MySQL, PostgreSQL and SQL Server enforce them by default.