Chapter 03 · basic · 6 min
Foreign keys and referential integrity
orders.customer_id is supposed to point at a row in customers. Nothing forces that unless you say so. A foreign key is that promise, made enforceable: Postgres checks it on every insert, and it has an opinion about what happens if you try to delete the row being pointed at.
The dataset
Two pairs of tables, same shape: customers/orders (a plain foreign key) and customers_cascade/orders_cascade (a foreign key declared ON DELETE CASCADE) — so the last two sections can show the same delete behaving two different ways.
Schema
| id | INT |
| name | TEXT |
| id | INT |
| customer_id | INT |
| amount | NUMERIC(10,2) |
| id | INT |
| name | TEXT |
| id | INT |
| customer_id | INT |
| amount | NUMERIC(10,2) |
Example data
A foreign key points at another table's primary key
orders.customer_id INT REFERENCES customers(id) declares that every value in that column must exist as an id in customers. This is the same relationship the JOINs chapter matched on — a foreign key is what makes it a guarantee instead of a coincidence.
Before you run it — both orders have a customer_id. Do you expect both to match a real row in customers?
Postgres checks the pointer on insert
Try to insert an order for a customer that doesn't exist.
Before you run it — there is no customer with id 99. Does the order still get created, pointing at nothing?
A valid pointer just works
Same insert, this time pointing at a real customer.
Before you run it — id 1 is a real customer this time. Does this one succeed?
By default, a referenced row can't be deleted out from under its orders
Aisyah (id 1) has orders pointing at her. Try to delete her.
Before you run it — order 101 points at customer 1. Does deleting customer 1 succeed and leave order 101 pointing at nothing, or does something stop it?
ON DELETE CASCADE changes what happens
orders_cascade.customer_id was declared with ON DELETE CASCADE instead of the default. Try the exact same delete that just failed, on this cascade-linked table instead.
Before you run it — customer 1 has an order pointing at them here too. Given what ON DELETE CASCADE means, do you expect this one to succeed where the last one didn't?
CASCADE deletes what you don't see happen
That succeeded — and it also silently deleted every order that pointed at customer 1, orders_cascade included. This playground reseeds its dataset on every run, so there's no second click that shows you the aftermath in the same session — but that's the actual trade CASCADE makes, worth naming plainly: it removes the friction of a blocked delete, and in exchange, deletes things you didn't directly ask it to touch. Read a schema's ON DELETE clauses before you trust a delete on it.
If you've used Excel or Google Sheets
Delete a row from a lookup sheet and every VLOOKUP/XLOOKUP that pointed at it doesn't error immediately — it just returns #N/A (or worse, silently matches the wrong row after everything shifts up). A foreign key is what a spreadsheet has no equivalent for: it stops the delete before it can create that orphan, or — with CASCADE — cleans up after it on purpose, instead of leaving broken references for someone to discover later.
A foreign key stops dangling pointers. It says nothing about whether the data on either side is well-organized in the first place — that's what the next two chapters, on normalization, are about.