A foreign key is a promise about reference
A foreign key says: what value sits in this column must already exist in that column of that table. orders.customer_id refs 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 doesn't 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 don't merge.
This is the value of the constraint and it's worth stating plainly: it turns a data-quality problem, which is found weeks later by a human, into a write error, which is found right away 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 dropping it would leave them pointing at nothing. The DB won't create the orphan.
Refusing is one of a few 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 drop thousands of orders, and a cascade that runs through a few tables can drop a great deal more than the person pressing the button expected.
Natural against surrogate
Given a table of people, is the main key the email address or an int that means nothing?
An email address is a natural key: it comes from the domain and carries meaning. It's also mutable, at times shared, at times absent, and awkward to index. each row that refs it stores a whole string, and a person changing their address means updating each one of them.
A meaningless int is a surrogate key. It's 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 main 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 a few columns. A table recording which student is enrolled on which course has a main key of (student_id, course_id): neither column alone spots a row, and the pair does. That declaration also states a business rule — a student can enrol on a course once — which the DB 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's a main key. The child side often isn't, and that omission turns cheap ops into full scans, because each 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 DB enforces the constraint right 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 wasn't, and each 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 each referencing table. A natural key. An email, a UUID string — is copied into each child row that refs it. So Its width is paid once per ref, 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 isn't just stable against change. It's seven times smaller across the refs, 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 doesn't also have to be the value scattered across a dozen tables. That is the thinking behind "surrogate main key plus a unique natural key" — stable, compact identity for the machine, and the business rule kept as well.
Questions people ask about Primary and Foreign Keys
Can a table have more than one primary key? No. A table has exactly one primary key, though it can span several columns. Other columns that must be unique can use a UNIQUE constraint instead.
Can a foreign key be NULL? Yes. A NULL foreign key means no reference, and the database skips the check for that row. Add NOT NULL to the column if every child must have a parent.
Does a foreign key create an index? Not on the child side. The parent key is indexed because it is a primary or unique key, but the referencing column needs its own index if you join or cascade on it.
Should I use an auto-increment integer or a UUID as the primary key? An integer is smaller and sorts in insert order, which keeps the index compact. A UUID is useful when ids are generated in many places, at the cost of larger keys and scattered inserts.
What happens if I delete a parent row that has children? By default the delete is refused. You can choose ON DELETE CASCADE to remove the children, or SET NULL to keep them with no parent.
Why does my foreign key not stop bad inserts in SQLite? SQLite leaves enforcement off unless you run PRAGMA foreign_keys = ON on each connection. Without it the REFERENCES clause is accepted and ignored.
Where it goes wrong with Primary and Foreign Keys
Turning foreign keys off for speed. They cost a thing on write. The cost of not having them is found in production, in data that no longer makes sense, and it's much harder to pay.
Losing the pragma. SQLite ships with foreign key enforcement off for backwards compatibility. A schema full of Refs clauses that were never enforced looks correct and ensures nothing.
Making the main key real. any of it that carries meaning can change, and a main key that changes has to be chased through each table that refs it.
Leaving the foreign key column unindexed. The parent side is indexed by its main key automatically. The child side often isn't, and each cascade or referential check then scans the whole child table.