Primary keys, foreign keys and relationships

A shop’s data does not sit in one big table. Customers, orders and products each get a table of their own, and keys link the tables together. A primary key is the column, or the set of columns, whose value picks out exactly one row of its table. A foreign key is a column that holds the primary key of a row in another table: orders.customer_id holds the customer_id of the customer who placed the order. Where the foreign key sits, and whether it has to be unique, decides the relationship between two tables: one-to-many, one-to-one or many-to-many. Every join in this module follows one of these links, so reading them correctly comes first.

To try an example yourself, press Run under it: its mart tables are created from scratch each time by the sql.js 1.14.2 build of SQLite, version 3.49.1, on this page. PostgreSQL 18 and MySQL 8.4 behave differently in a few places that matter, and the lesson says where.

A primary key picks out one row

A primary key makes two promises: no two rows share a key value, and no row goes without one. A table has at most one primary key, made of one column or of several together. Try breaking the first promise:

-- customer_id is the primary key: it names exactly one customer.
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL
);
INSERT INTO customers (customer_id, name, city) VALUES
  (1, 'Asha Kulkarni', 'Pune'),
  (2, 'Vikram Nair',   'Kochi');

-- A second customer 1 is refused, and the table keeps its two rows.
INSERT INTO customers (customer_id, name, city) VALUES (1, 'Asha K.', 'Mumbai');
SELECT * FROM customers;

┌─────────────┬───────────────┬───────┐
│ customer_id │     name      │ city  │
├─────────────┼───────────────┼───────┤
│ 1           │ Asha Kulkarni │ Pune  │
│ 2           │ Vikram Nair   │ Kochi │
└─────────────┴───────────────┴───────┘

Runtime error near line 12: UNIQUE constraint failed: customers.customer_id

SQLite refuses the second customer 1 with “UNIQUE constraint failed”, and the table still holds its two rows: a statement that breaks a constraint changes nothing. PostgreSQL reports the same mistake as a duplicate key value that violates the constraint customers_pkey, and MySQL as error 1062, “Duplicate entry ‘1’ for key ‘customers.PRIMARY’”.

A key can span columns. In order_items the key is (order_id, product_id): an order holds many products and a product sits in many orders, so each column repeats on its own, but a given pair appears only once. That pair is what stops the same product from being listed twice in one order.

SQLite lets NULL into some primary keys

Standard SQL never allows NULL in a primary key. SQLite departs from that to stay compatible with programs written for its early versions, which had a bug here:

-- A text primary key in an ordinary SQLite table: NULL gets in, twice.
CREATE TABLE coupons (
  code        TEXT    PRIMARY KEY,
  discount_pc INTEGER NOT NULL
);
INSERT INTO coupons (code, discount_pc) VALUES ('DIWALI10', 10), (NULL, 15), (NULL, 20);
SELECT count(*) AS coupons, count(code) AS with_a_code FROM coupons;

-- With NOT NULL written out, SQLite refuses NULL as PostgreSQL and MySQL do.
CREATE TABLE coupons_checked (
  code        TEXT    NOT NULL PRIMARY KEY,
  discount_pc INTEGER NOT NULL
);
INSERT INTO coupons_checked (code, discount_pc) VALUES (NULL, 15);

┌─────────┬─────────────┐
│ coupons │ with_a_code │
├─────────┼─────────────┤
│ 3       │ 1           │
└─────────┴─────────────┘

Runtime error near line 14: NOT NULL constraint failed: coupons_checked.code

Three coupons went in, and only one of them has a code. In an ordinary SQLite table, a primary key column accepts NULL unless the column is an INTEGER PRIMARY KEY, is declared NOT NULL, or belongs to a STRICT or WITHOUT ROWID table. Uniqueness does not stop the second NULL either, because two NULLs never count as equal. PostgreSQL and MySQL mark every primary key column NOT NULL by themselves. In SQLite, write NOT NULL next to any primary key that is not an INTEGER PRIMARY KEY, as the second table does.

Choosing what the key is

A natural key is a value that people outside the database already use to tell things apart, such as a passport number, the barcode on a product or a user’s login name. A surrogate key carries no meaning at all: the database makes it up, the way it numbers customer_id. Foreign keys copy the key into other tables, so a key that changes has to change in every one of them. People change their email addresses and phone numbers; a number the database made up never needs to change. That is why most schemas give each table a surrogate key and keep natural values unique with a UNIQUE constraint. The schema design module comes back to this choice.

A foreign key points to a row of another table

Writing customer_id INTEGER NOT NULL REFERENCES customers (customer_id) makes orders.customer_id a foreign key. orders is the child table, the one that refers, and customers is the parent table, the one referred to. Three rules follow:

• Every value in the child column must exist as a key in the parent. NULL is allowed unless the column is NOT NULL, and it means that the row has no parent.
• A parent row cannot be deleted while child rows point to it, unless the foreign key names an action such as ON DELETE CASCADE. The module on constraints covers those actions.
• The parent’s columns must be its primary key or carry a UNIQUE constraint, so that a value points to one row and not to several. PostgreSQL rejects any other foreign key with “there is no unique constraint matching given keys”, and MySQL 8.4 refuses it too unless the deprecated setting restrict_fk_on_non_standard_key is switched off.

SQLite adds a rule of its own, and it surprises most people who meet it:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL
);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT    NOT NULL
);
INSERT INTO customers (customer_id, name) VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair');

-- A new SQLite connection does not check foreign keys: an order for customer 99 goes in.
PRAGMA foreign_keys;
INSERT INTO orders (order_id, customer_id, status) VALUES (101, 1, 'delivered'), (102, 99, 'placed');

-- Switched on, the checks refuse the same mistake, and a customer who still has orders.
PRAGMA foreign_keys = ON;
INSERT INTO orders (order_id, customer_id, status) VALUES (103, 98, 'placed');
DELETE FROM customers WHERE customer_id = 1;

-- Order 102 got in before the checks were on. foreign_key_check finds it.
SELECT * FROM pragma_foreign_key_check('orders');

┌──────────────┐
│ foreign_keys │
├──────────────┤
│ 0            │
└──────────────┘
┌────────┬───────┬───────────┬──────┐
│ table  │ rowid │  parent   │ fkid │
├────────┼───────┼───────────┼──────┤
│ orders │ 102   │ customers │ 0    │
└────────┴───────┴───────────┴──────┘

Runtime error near line 18: FOREIGN KEY constraint failed
Runtime error near line 19: FOREIGN KEY constraint failed

Step by step:

• PRAGMA foreign_keys returns 0: a new SQLite connection does not check foreign keys, for compatibility with old programs. The order for customer 99 went in, an order that belongs to nobody.
• After PRAGMA foreign_keys = ON, the order for customer 98 is refused, and so is deleting Asha, who still has order 101.
• Switching the checks on does not look at rows that are already there. pragma_foreign_key_check('orders') lists every row that breaks a foreign key: the table, the row’s rowid (here the order_id, 102), the parent table and the number of the foreign key that failed.

The setting belongs to the connection, not to the database file. Every program that opens the file has to switch it on again, and data written by a program that did not can already hold such orphans.

Index the foreign key column

Looking up the children of a parent, to check a delete or to join the two tables, reads the child column. PostgreSQL and SQLite do not index that column for you, and both manuals recommend adding an index. MySQL needs one for every foreign key, and creates it when the table has none. The module on indexes shows what the index changes.

Three shapes of relationship

The diagram draws the three shapes with mart tables, showing only their key columns.

One-to-many

This is the most common shape. The foreign key sits on the “many” side: one customer has many orders, so orders carries customer_id, while customers stores nothing about orders. NOT NULL on the foreign key says that every order must have a customer; without it, an order could have none.

One-to-one

A foreign key that is also unique can name each parent row only once, so a parent has at most one child:

PRAGMA foreign_keys = ON;
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  status   TEXT    NOT NULL
);
-- UNIQUE on the foreign key: an order can have one invoice at most.
CREATE TABLE invoices (
  invoice_no TEXT    NOT NULL PRIMARY KEY,
  order_id   INTEGER NOT NULL UNIQUE REFERENCES orders (order_id)
);
INSERT INTO orders (order_id, status) VALUES (101, 'delivered'), (102, 'delivered'), (103, 'placed');
INSERT INTO invoices (invoice_no, order_id) VALUES ('MRT-0001', 101), ('MRT-0002', 102);

-- A second invoice for order 101 would make the link one-to-many.
INSERT INTO invoices (invoice_no, order_id) VALUES ('MRT-0003', 101);
SELECT * FROM invoices;

┌────────────┬──────────┐
│ invoice_no │ order_id │
├────────────┼──────────┤
│ MRT-0001   │ 101      │
│ MRT-0002   │ 102      │
└────────────┴──────────┘

Runtime error near line 15: UNIQUE constraint failed: invoices.order_id

The second invoice for order 101 is refused. Tables are split one-to-one when the extra columns are optional and most rows would leave them empty, when they are read far less often than the rest, or when they need different permissions.

Many-to-many

This shape does not fit into either table, because a column holds one value per row: an order cannot list its products in one column, nor a product its orders. A junction table holds one row per pair, with a foreign key to each side. order_items is the junction table of orders and products. Its primary key is the pair, and it carries facts about the pair, such as the quantity.

Reading a relationship

From the data

Counting from both ends of a link shows how it is used:

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status      TEXT    NOT NULL
);
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
INSERT INTO orders (order_id, customer_id, status) VALUES
  (101, 1, 'delivered'), (102, 2, 'delivered'), (103, 1, 'placed'), (104, 3, 'cancelled');
INSERT INTO order_items (order_id, product_id, quantity) VALUES
  (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3);

-- Customers to orders: how many orders per customer?
SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

-- Orders to products, read from both ends of order_items.
SELECT order_id, count(*) AS products
FROM order_items
GROUP BY order_id
ORDER BY order_id;

SELECT product_id, count(*) AS orders
FROM order_items
GROUP BY product_id
ORDER BY product_id;

┌─────────────┬────────┐
│ customer_id │ orders │
├─────────────┼────────┤
│ 1           │ 2      │
│ 2           │ 1      │
│ 3           │ 1      │
└─────────────┴────────┘
┌──────────┬──────────┐
│ order_id │ products │
├──────────┼──────────┤
│ 101      │ 2        │
│ 102      │ 1        │
│ 103      │ 2        │
│ 104      │ 1        │
└──────────┴──────────┘
┌────────────┬────────┐
│ product_id │ orders │
├────────────┼────────┤
│ 1          │ 1      │
│ 2          │ 2      │
│ 3          │ 1      │
│ 4          │ 1      │
│ 5          │ 1      │
└────────────┴────────┘

Customer 1 has two orders, while each order has exactly one customer_id: one-to-many. Order 101 holds two products and product 2 appears in two orders, so order_items connects many to many. Data only shows what happens to be stored today, though. A count of 1 on both sides does not make a link one-to-one; only a UNIQUE constraint does, and that lives in the schema.

From the catalog

The catalog tells you what the schema allows. For the invoices table of the one-to-one example:

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  status   TEXT    NOT NULL
);
CREATE TABLE invoices (
  invoice_no TEXT    NOT NULL PRIMARY KEY,
  order_id   INTEGER NOT NULL UNIQUE REFERENCES orders (order_id)
);

-- Where does invoices point, and which of its columns must be unique?
SELECT "from", "table", "to" FROM pragma_foreign_key_list('invoices');
SELECT name, "unique", origin FROM pragma_index_list('invoices') ORDER BY name;
SELECT name AS column_name FROM pragma_index_info('sqlite_autoindex_invoices_2');

┌──────────┬────────┬──────────┐
│   from   │ table  │    to    │
├──────────┼────────┼──────────┤
│ order_id │ orders │ order_id │
└──────────┴────────┴──────────┘
┌─────────────────────────────┬────────┬────────┐
│            name             │ unique │ origin │
├─────────────────────────────┼────────┼────────┤
│ sqlite_autoindex_invoices_1 │ 1      │ pk     │
│ sqlite_autoindex_invoices_2 │ 1      │ u      │
└─────────────────────────────┴────────┴────────┘
┌─────────────┐
│ column_name │
├─────────────┤
│ order_id    │
└─────────────┘

pragma_foreign_key_list says that invoices.order_id points to orders.order_id. pragma_index_list lists the table’s indexes: origin is pk for the index of the primary key and u for one that a UNIQUE constraint made. pragma_index_info names the columns of an index, here order_id alone. A foreign key whose columns are exactly the columns of a unique index is a one-to-one link. In PostgreSQL and MySQL the same facts are in information_schema.table_constraints and information_schema.key_column_usage.

Why split data across tables

Splitting looks like extra work until you see what a single wide table does when facts change:

-- One wide table: every line of an order repeats who bought it and where they live.
CREATE TABLE order_lines (
  order_id INTEGER,
  customer TEXT,
  city     TEXT,
  product  TEXT,
  quantity INTEGER
);
INSERT INTO order_lines VALUES
  (101, 'Asha Kulkarni', 'Pune',  'Basmati rice 1 kg', 2),
  (101, 'Asha Kulkarni', 'Pune',  'Toor dal 500 g',    1),
  (102, 'Vikram Nair',   'Kochi', 'Coconut oil 1 L',   1),
  (103, 'Asha Kulkarni', 'Pune',  'Masala chai 250 g', 2),
  (103, 'Asha Kulkarni', 'Pune',  'Ragi flour 1 kg',   1);

-- Asha moves to Mumbai. The update reaches the lines of one order and misses the other.
UPDATE order_lines SET city = 'Mumbai' WHERE order_id = 101;

SELECT DISTINCT customer, city
FROM order_lines
ORDER BY customer, city;

┌───────────────┬────────┐
│   customer    │  city  │
├───────────────┼────────┤
│ Asha Kulkarni │ Mumbai │
│ Asha Kulkarni │ Pune   │
│ Vikram Nair   │ Kochi  │
└───────────────┴────────┘

The wide table writes Asha’s city on every line of every order she placed. The update changed it on the lines of order 101 and missed order 103, so the table now says she lives in two cities at once. This is an update anomaly, and it has two relatives:

• Insertion: a customer who has not ordered yet cannot be stored, because every row is an order line.
• Deletion: deleting a customer’s only order also deletes the only record of the customer.

In the mart tables a city is written once, in one customers row, and each order stores just the customer_id. Moving Asha to Mumbai is one UPDATE of one row, and a join puts the facts back together when a query needs them. A rule of thumb: when the same fact is written on many rows, it belongs in a table of its own. The design module turns that rule into the normal forms.

How the engines differ

Interview questions

Fresher · What is the difference between a primary key and a UNIQUE constraint?

Both stop two rows from sharing a value. A table has at most one primary key but any number of UNIQUE constraints. Primary key columns can never hold NULL, while a UNIQUE column accepts NULLs, and by default two NULLs do not count as duplicates, so many rows may leave the column empty. The primary key is the row’s identity: a foreign key written without a column list refers to it in PostgreSQL and SQLite, and in MySQL’s InnoDB engine the table’s rows are stored in primary key order. Use the primary key for the identifier that other tables point to, and UNIQUE for other values that must not repeat, such as an email address. One SQLite exception: an ordinary table accepts NULL in a primary key column that is not INTEGER PRIMARY KEY unless it is declared NOT NULL.

Key takeaways

• A primary key is unique and never NULL, at most one per table, and it may span several columns, as (order_id, product_id) does.
• A foreign key holds a parent’s key, which must be the parent’s primary key or unique; NULL means “no parent” unless the column is NOT NULL.
• SQLite checks foreign keys only after PRAGMA foreign_keys = ON on each connection, and pragma_foreign_key_check finds rows that slipped in before.
• MySQL 8.4 ignores REFERENCES written after a column; the separate FOREIGN KEY (…) REFERENCES … clause works in every engine.
• The foreign key sits on the many side of a one-to-many link; a unique foreign key makes a link one-to-one; a junction table with two foreign keys stores a many-to-many link.
• Store each fact once: repeated facts drift apart when one copy changes and the others do not.