Primary and Foreign Keys
The two constraints that stop a database from holding rows that cannot be true. Try to break them and watch the insert be refused.
Overview
A key is a promise about identity
A primary key is the column, or set of columns, whose value identifies a row uniquely and permanently. Declaring one asks the database for two guarantees at once: no two rows share this value, and no row leaves it NULL.
That second half is easy to overlook and does real work. NULL means unknown. A row whose identity is unknown cannot be referred to, updated with confidence, or joined against, so a nullable identifier is not an identifier at all.
Run the first statement in the editor. The insert is refused, and the message comes from SQLite rather than from anything on this page: UNIQUE constraint failed: orders.id. Nothing in the application had to check first, and no amount of concurrent traffic can slip a duplicate past it.
Primary and Foreign Keys
Worth knowing
PRAGMA foreign_keys = ON. Most engines enforce them by default.Primary and Foreign Keys
The two constraints that make a row impossible to write if it could not be true.
A foreign key is a promise about reference
A foreign key says: whatever value sits in this column must already exist in that column of that table. orders.customer_id references customers.id, so an order can only belong to a customer who is really there.
Uncomment the second statement and run it. Order 9 claims customer 77, and customer 77 does not exist, so the write fails. Without the constraint the row would be accepted and the problem would surface much later, as a join that silently returns fewer rows than expected or a report whose totals do not reconcile.
This is the value of the constraint and it is worth stating plainly: it turns a data-quality problem, which is discovered weeks later by a human, into a write error, which is discovered immediately by a machine.
What happens to the other side
Uncomment the third statement. Deleting customer 1 is refused, because orders 1 and 2 point at that customer and removing it would leave them pointing at nothing. The database will not create the orphan.
Refusing is one of several available answers, chosen with ON DELETE:
| Clause | Behaviour |
|---|---|
RESTRICT / NO ACTION | refuse the delete while children exist (the default) |
CASCADE | delete the children too |
SET NULL | keep the children, blank the reference |
SET DEFAULT | point the children at a default row |
CASCADE is convenient and worth respecting. Deleting one customer can silently remove thousands of orders, and a cascade that runs through several tables can remove a great deal more than the person pressing the button expected.
Natural against surrogate
Given a table of people, is the primary key the email address or an integer that means nothing?
An email address is a natural key: it comes from the domain and carries meaning. It is also mutable, occasionally shared, sometimes absent, and awkward to index. Every row that references it stores a whole string, and a person changing their address means updating every one of them.
A meaningless integer is a surrogate key. It is stable because nothing in the world can force it to change, compact to store and to index, and safe to scatter across a dozen referencing tables.
The usual practice is a surrogate primary key plus a UNIQUE constraint on the natural one, which buys stable identity and still refuses two accounts on the same address.
Composite keys
A key can span several columns. A table recording which student is enrolled on which course has a primary key of (student_id, course_id): neither column alone identifies a row, and the pair does. That declaration also states a business rule — a student can enrol on a course once — which the database will now enforce for free.
Why the child side needs its own index
The parent side of a foreign key is indexed for free — it is a primary key. The child side usually is not, and that omission turns cheap operations into full scans, because every referential action has to ask "are there children?" and answer it by looking.
Delete a customer with 5,000,000 orders in the child table. With no index on orders.customer_id:
| operation | with no index on the child FK | with the index |
|---|---|---|
ON DELETE RESTRICT check | scan up to 5,000,000 rows | a single index seek |
ON DELETE CASCADE | scan to find the children | seek straight to them |
a join customers ⋈ orders | scan the child table | indexed lookup |
The database enforces the constraint correctly either way; the difference is whether each check is a seek or a scan of the whole child table. This is the quiet reason a schema "with foreign keys" can still be slow: the constraint was declared, the supporting index on the referencing column was not, and every delete or cascade pays for it.
Why the surrogate key is usually smaller too
The natural-versus-surrogate choice has a storage side that compounds with every referencing table. A natural key — an email, a UUID string — is copied into every child row that references it, so its width is paid once per reference, not once per person:
| key in 5 child tables, 10M rows each | storage in the children |
|---|---|
| email as the foreign key (~30 bytes) | ~1,500 MB |
| surrogate integer (4 bytes) | ~200 MB |
The surrogate is not just stable against change — it is seven times smaller across the references, and a smaller key means more index entries per page, so the indexes that use it are smaller and shallower too. The natural key still earns a UNIQUE constraint so the real-world rule ("one account per email") is enforced; it simply does not also have to be the value scattered across a dozen tables. That is the reasoning behind "surrogate primary key plus a unique natural key" — stable, compact identity for the machine, and the business rule kept as well.
Where it goes wrong
Turning foreign keys off for performance. They cost something on write. The cost of not having them is discovered in production, in data that no longer makes sense, and it is much harder to pay.
Forgetting the pragma. SQLite ships with foreign key enforcement off for backwards compatibility. A schema full of REFERENCES clauses that were never enforced looks correct and guarantees nothing.
Making the primary key meaningful. Anything that carries meaning can change, and a primary key that changes has to be chased through every table that references it.
Leaving the foreign key column unindexed. The parent side is indexed by its primary key automatically. The child side usually is not, and every cascade or referential check then scans the whole child table.
Check yourself
0 of …Answer without scrolling back up.
What two guarantees does declaring a primary key give you?
Why is a surrogate integer usually preferred over an email address as a primary key?
Why does SQLite in particular need `PRAGMA foreign_keys = ON`?
Primary and Foreign Keys
A primary key is the column, or set of columns, whose value identifies a row uniquely and permanently. Declaring one asks the database for two guarantees at once: no two rows share this value, and no row leaves it NULL.