Data Engineer · #053 · September 14, 2026 · 1 min read
Primary vs foreign vs surrogate keys, explained
The four kinds of database keys, why surrogates win the primary-key job, composite keys, what ON DELETE CASCADE really deletes, and the warehouse rules on top: keys on one page.
Get the free PDF
One page, print-ready, free to share. No signup needed.
The question every SQL interview asks, sooner or later: what should the primary key be? The four kinds of keys, on one page. The print-ready A4 PDF is at the bottom.
The four
- Primary: unique + not null, one per table.
- Foreign: points at a primary key, enforced by the database.
- Natural: means something (an email, an order number).
- Surrogate: means nothing (id 42), and that is the point.
Pick one
- Surrogate for the PK: boring wins.
- Emails change. Natural keys rot.
- int vs UUID: compact vs distributed-friendly.
In SQL
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY,
order_no TEXT NOT NULL UNIQUE, -- natural
customer_id BIGINT NOT NULL
REFERENCES customers(id), -- foreign
PRIMARY KEY (id) -- surrogate
);
The natural key still earns a UNIQUE constraint. It just does not get the PK job, because business facts change.
Composite
(order_id, line_no): one key, two columns.- Junction tables: how many-to-many works.
UNIQUE (a, b): the pair never repeats.
Warehouse rules
- Surrogate keys in dimensions: SCD needs them.
- Join facts to dims on surrogates, never on natural keys.
- A reserved unknown row (key -1) catches late-arriving facts.
The trap: CASCADE roulette
| Clause | Delete a customer and | Use when |
|---|---|---|
| CASCADE | all their orders vanish too | true ownership only |
| RESTRICT | the delete is refused | the safe default |
| SET NULL | orders lose their customer | links are optional |
Interview phrasing: CASCADE is not wrong, it is a blast radius. Default to RESTRICT and cascade only inside an aggregate.
Frequently asked questions
What is the difference between a primary key and a foreign key?
A primary key uniquely identifies each row in its own table: unique and not null, one per table. A foreign key is a column in another table that points at that primary key, and the database enforces the link: you cannot reference a customer that does not exist. Primary keys identify, foreign keys connect.
What is a surrogate key and why use one instead of a natural key?
A natural key means something in the real world (an email, an order number); a surrogate key means nothing (id 42, a generated integer or UUID). Surrogates win the primary-key job because business facts change: people change emails, companies renumber orders. The natural key still deserves a UNIQUE constraint, it just should not be the PK.
What does ON DELETE CASCADE do?
It decides what happens to child rows when the parent is deleted: CASCADE deletes them all (delete a customer, their orders vanish), RESTRICT refuses the delete while children exist, SET NULL orphans the link on purpose. CASCADE is not wrong, it is a blast radius: default to RESTRICT and cascade only where the child truly cannot exist without the parent.
Why do data warehouses use surrogate keys in dimensions?
Because slowly changing dimensions need them: when a customer's segment changes and you keep both versions of the row, the natural key is now duplicated and only a surrogate can tell the versions apart. Facts join dimensions on surrogates, never on natural keys, and a reserved row (often key -1) catches late-arriving facts.
Get the free PDF
One page, print-ready, free to share. No signup needed.