Primary & Foreign Keys

Primary keys give every row an identity; foreign keys tie rows in different tables together and guarantee the links are valid. Together they implement the relationships from lesson 22 and protect your data's referential integrity.

Primary keys

CREATE TABLE countries (
  code CHAR(2) PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO countries VALUES ('PT', 'Portugal'), ('ES', 'Spain');
INSERT INTO countries VALUES ('PT', 'Portugal again');

Error: UNIQUE constraint failed: countries.code

A primary key is unique and not null. A table can have only one, though it may span several columns. (SQLite has a historical quirk: non-integer primary key columns allow NULL unless also declared NOT NULL or the table is STRICT.)

Composite primary keys

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(8, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

From the demo database: the pair (order, product) identifies a row, so the same product can't appear twice in one order.

Surrogate or natural keys?

A natural key is real-world data that's unique (a country code, an ISBN). A surrogate key is a meaningless generated number or UUID. Surrogates are the usual choice: they never need to change, and they're compact. Natural values that must be unique still get a UNIQUE constraint.

Foreign keys

INSERT INTO orders (id, customer_id, order_date, status)
VALUES (16, 99, '2026-03-12', 'pending');

Error: FOREIGN KEY constraint failed

customer_id INTEGER NOT NULL REFERENCES customers(id) means every value must exist in customers.id. There's no customer 99, so the insert is rejected. The referenced column must be a primary key or unique.

Defining foreign keys

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  CONSTRAINT fk_books_author FOREIGN KEY (author_id) REFERENCES authors(id)
);

INSERT INTO authors VALUES (1, 'R. Okoye');
INSERT INTO books (title, author_id) VALUES ('Small Rivers', 1);

SELECT b.title, a.name FROM books AS b JOIN authors AS a ON a.id = b.author_id;

The inline form (REFERENCES authors(id) after the column) and the table-constraint form shown here are equivalent; the latter can be named and can span several columns.

What happens when the parent changes?

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE comments (
  id INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  body TEXT NOT NULL
);

INSERT INTO posts VALUES (1, 'Hello'), (2, 'Second post');
INSERT INTO comments (post_id, body) VALUES (1, 'Nice!'), (1, 'Thanks'), (2, 'First');

DELETE FROM posts WHERE id = 1;

SELECT * FROM comments;

Deleting post 1 removed its two comments automatically. CASCADE suits rows that can't exist without their parent (comments, order items). For important data like orders, the default — refusing the delete — is safer. ON UPDATE works the same way when a parent key changes.

Self-referencing keys

manager_id INTEGER REFERENCES employees(id)

A foreign key can point at its own table, as employees.manager_id does. It's nullable so the person at the top can have no manager.

Foreign keys and indexes

Databases automatically index primary keys, but most (PostgreSQL, SQL Server, SQLite) don't automatically index foreign-key columns. Since joins and cascades search by them, add an index on foreign-key columns of large tables (lesson 46). MySQL's InnoDB creates them for you.

SQLite note

For backwards compatibility, SQLite enforces foreign keys only after PRAGMA foreign_keys = ON; on each connection. The editors in this course turn it on for you.

Try it yourself

Create playlists and songs with a many-to-many link table, using cascading deletes on the link.