What is a foreign key — and why it quietly slows down deletes

Here's a funny thing. You can write user_id = 999 into your orders table when user 999 never existed. The database will happily take it. And you'll find out a month later — when the order page shows a blank space where a name should be.
A foreign key is how you tell the database: don't accept that at all. Not a check in your code that someone can forget. A rule baked into the data itself.
What a foreign key actually is
A foreign key is a column that must point at an existing row in another table.
You have users and orders. An order carries a user_id. You declare: values in orders.user_id must exist in users.id. That's it.
The PostgreSQL docs describe the outcome literally: it becomes impossible to create an order whose product doesn't appear in the products table. Not "discouraged" — impossible. The insert simply fails.
The table being pointed at is the parent. The one pointing is the child. An order is a child of a user.
Why bother, if your code already writes user_id correctly? Because "correctly" is your code. The database is also touched by your admin panel, a one-off migration script, and you at 2am in a SQL console. A foreign key is the one check nobody can walk past.
What happens when the parent is deleted
This is where it gets interesting. A user deletes their account. They have forty orders pointing at them. What should the database do?
You decide upfront, with one word in the key definition:
- NO ACTION — the default. Deleting the parent "will usually result in an error", as the docs put it plainly. The database refuses until you deal with the children.
- RESTRICT — same, but stricter: the check can't be deferred to the end of the transaction.
- CASCADE — delete the children automatically. User goes, their orders go.
- SET NULL — keep the orders, but null out the link. The order exists, the owner doesn't.
- SET DEFAULT — same idea, but fill in the column's default.
Beginners almost always reach for CASCADE — it's convenient, nothing gets in the way. Think twice. CASCADE on orders means deleting one user wipes payment history. Sometimes that's right (the law may require deletion). Sometimes it's a disaster nobody notices until the quarterly report.
A decent rule: CASCADE for things that are meaningless without the parent (profile photo, settings, tokens). RESTRICT for things that matter on their own (orders, payments, messages).
The trap: a foreign key does not create an index
This is the part nearly nobody expects.
It feels logical: I declared a relationship, so surely the database prepared to look it up quickly. It didn't. The PostgreSQL docs say it outright: "the declaration of a foreign key constraint does not automatically create an index on the referencing columns."
Why does that matter? Because deleting a parent forces the database to check whether anything still references it. And the same docs explain: a delete in the referenced table "will require a scan of the referencing table", so "it is often a good idea to index the referencing columns too."
In plain terms: you delete one user, and the database dutifully reads the entire orders table front to back to make sure none of their orders are left. At a thousand rows you won't notice. At a million, the delete hangs for seconds and you go hunting for the cause everywhere except here.
The fix is one line: put a plain index on orders.user_id. The parent's primary key is indexed for you; the child column is not. Remember that asymmetry — it saves an evening of debugging.
When people skip foreign keys
Honestly: not everyone uses them. There are arguments against, and it's worth knowing them rather than just the "correct" answer.
First: bulk-load speed. Every insert carries an extra check. When loading a million rows at once, keys are sometimes dropped temporarily.
Second: they get in the way of deleting test data. That's not an argument, it's a symptom — it usually means the delete order simply wasn't thought through.
Third, and real: non-relational databases often don't offer foreign keys at all. There, integrity lives in the application, and that's a deliberate trade for flexibility.
For a first project the advice is simple: use them. The cost is one line in a migration. The payoff is a database with no orphaned orders. Orphans always show up — the only question is when.
By the way, the whole idea of "keep the user's name in one place and let the order just point at it" is normalization. The foreign key is how you enforce it.
Where you'll meet one first
The moment your project gets a second table linked to the first. A user's notes, comments on a post, items in an order.
In Supabase you add a foreign key with a checkbox when creating the column — the delete behaviour is chosen right there. In an ORM like Prisma or Django it appears when you describe a relation between models: the library turns it into a real key in the database.
Is a foreign key the same as a primary key?
No. A primary key identifies a row inside its own table. A foreign key points at a row in someone else's. A foreign key usually references the neighbouring table's primary key.
Can a foreign key be empty?
Yes, if the column allows NULL. An empty value means "no link" and is permitted. The check only kicks in for non-empty values: those must exist in the parent.
The key is there but the data is still a mess — how?
Most likely the key was added after the mess existed. The database enforces new changes, and it validates existing rows once when the key is added — if they fail, the migration fails. So you clean the data first, then add the key.
The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.





