Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 43 of 49

Primary keys and foreign keys: identifying rows and linking tables

Learn what primary keys and foreign keys do, how auto-numbered ids work, and what ON DELETE CASCADE and SET NULL mean, with verified SQLite output.

16 minDesigning databasesUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonChoose a primary key for a table, link two tables with a foreign key, and predict what happens when a referenced row is deleted.

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.

sql
CREATE TABLE authors (
  id   INTEGER PRIMARY KEY,
  name VARCHAR(60) NOT NULL
);

INSERT INTO authors (name) VALUES ('Leela Menon'), ('Jay Parekh');
SELECT * FROM authors;
result
+----+-------------+
| 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 KEY fills itself in. The AUTOINCREMENT keyword 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 older SERIAL.
  • 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:

sql
INSERT INTO authors (id, name) VALUES (1, 'Duplicate');
result
Error: UNIQUE constraint failed: authors.id

Choosing 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:

sql
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:

sql
INSERT INTO enrolments VALUES (1, 100, '2026-03-01');
result
Error: UNIQUE constraint failed: enrolments.student_id, enrolments.course_id

Foreign 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:

sql
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:

sql
INSERT INTO books (title, author_id) VALUES ('Ghost Book', 99);
result
Error: FOREIGN KEY constraint failed

The 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:

sql
DELETE FROM authors WHERE id = 1;
result
Error: FOREIGN KEY constraint failed

The 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 ACTION or RESTRICT: reject the delete (the default).
  • SET NULL: keep the children and clear their link. The column must allow NULL.
  • CASCADE: delete the children as well.

Here are both of the last two on a small example:

sql
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;
result
+----+------+---------+
| id | name | team_id |
+----+------+---------+
|  1 | Uma  | NULL    |
|  2 | Vik  | NULL    |
|  3 | Wes  |       2 |
+----+------+---------+
result
+----+---------+-----------+
| 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 = ON in 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 CASCADE by default. Decide per relationship, and prefer rejecting the delete when the child rows matter.
  • Making a foreign key column NOT NULL when 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
sql
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;
result
+----+-------------+--------+
| id | customer_id | amount |
+----+-------------+--------+
|  1 |           1 |   5000 |
+----+-------------+--------+
sql
INSERT INTO invoices (customer_id, amount) VALUES (7, 100);
result
Error: FOREIGN KEY constraint failed

Key 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.

Check yourself

1. Which rule does a primary key combine?
2. A foreign key from books.author_id to authors.id exists. What stops you inserting a book with author_id 99 when there is no such author?
3. A team is deleted. Players have team_id with ON DELETE SET NULL. What happens to those players?
4. Why is ON DELETE CASCADE risky?
5. In SQLite, you add a foreign key but never run PRAGMA foreign_keys = ON. What happens when you insert a row that points at a missing parent?