Databases
Foreign key design: interview questions and how to answer them
A foreign key guarantees the referenced row exists; the design decisions are what happens on delete, and what it locks along the way.
Written and reviewed by Sahil Srivastav
What it actually is
A foreign key declares that a column's values must exist as keys in another table. The database then refuses any write that would break it: you cannot insert a child pointing at a non-existent parent, and you cannot delete a parent that still has children unless you have specified what should happen instead.
The guarantee is stronger than it first appears because it covers both directions and all writers. Orphaned rows — a child pointing at an id that no longer exists — are a classic source of code that crashes on a null it believed impossible, and they accumulate silently in schemas without foreign keys. Once declared, they cannot occur.
The design work is almost entirely in the referential action: NO ACTION or RESTRICT to refuse the delete, CASCADE to delete children too, SET NULL to orphan them deliberately, SET DEFAULT to reassign. Each encodes a different statement about what the relationship means, and choosing by convenience rather than by meaning is how data gets destroyed.
Why it matters in production
Because the two failure modes are both severe and opposite. Without foreign keys, you get silent orphans and code that breaks on data it assumed could not exist. With carelessly chosen cascades, you get the opposite problem: deleting one row removes thousands across tables you were not thinking about, as a single transaction, discovered after the fact.
There is also a performance dimension that catches people out. Foreign keys take locks on parent rows during child inserts, and enforcing them on delete requires finding the children — which, without an index on the referencing column, is a full scan of the child table per deleted parent. Both of these show up as mysterious contention rather than as anything resembling a foreign key problem.
How it works
The referencing column needs its own index
PostgreSQL indexes the referenced primary key automatically and does not index the referencing column. So deleting a parent, or updating its key, must scan the child table to check for references. On a large child table this turns a single-row delete into a sequential scan — one of the most common and most surprising slow-delete causes.
CASCADE is a multiplier, not a convenience
ON DELETE CASCADE deletes all children, and their children, transitively, in one transaction. Deleting a tenant can mean millions of rows, a very long lock hold, and a large WAL burst. It is right when the child genuinely cannot exist independently — order lines without an order — and dangerous when the relationship is weaker than that.
SET NULL means the relationship is optional
Only valid when the column is nullable, and it encodes that the child outlives the parent meaningfully — an audit row whose actor was deleted, say. The thing to check is whether downstream code handles the null, because this is precisely how a column that "is always set" becomes sometimes null.
Parent row locking during child insert
Inserting a child takes a lock on the referenced parent row to prevent it disappearing before commit. Modern PostgreSQL uses a weak lock that allows concurrent inserts referencing the same parent, but it still participates in deadlock cycles when two transactions touch parents in different orders — a deadlock whose DETAIL names rows neither statement mentioned.
Soft deletes defeat the guarantee
With a deleted_at column, the parent row still exists, so the foreign key is satisfied while the parent is logically gone. The database can no longer enforce the rule you actually care about. If you soft delete, the "is the parent alive?" check moves into application code — and that is a deliberate trade, not a free one.
Implementing it
Index every foreign key column unless you can show the parent is never deleted or updated. It is the cheapest fix for a class of slow delete that is otherwise hard to attribute.
Choose the referential action from the meaning of the relationship. Composition — the child is part of the parent — justifies CASCADE. Association justifies RESTRICT and an explicit cleanup path, so nobody deletes a thousand rows by accident.
For bulk deletes of cascading parents, delete in batches rather than one transaction. A single statement removing millions of descendant rows holds locks and produces WAL for the whole duration.
If you use soft deletes, decide explicitly where the liveness check lives and apply it consistently — ideally in a view or a repository layer that every reader goes through, rather than in each query.
-- Composition: a line cannot exist without its order. CASCADE is honest here.
CREATE TABLE order_items (
id bigserial PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
sku text NOT NULL
);
-- Required, and almost always forgotten: PostgreSQL does NOT create this.
-- Without it, deleting one order sequentially scans order_items.
CREATE INDEX order_items_order_id_idx ON order_items (order_id);
-- Association: deleting a customer should NOT silently delete their orders.
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;Interview questions and how to answer them
Why is deleting a parent row slow?
Almost always a missing index on the child's referencing column. The database must verify no children reference the row, and without an index that is a sequential scan of the child table per parent deleted. PostgreSQL indexes the referenced key automatically but not the referencing one, so this has to be done deliberately.
When is ON DELETE CASCADE appropriate?
When the child genuinely cannot exist without the parent — order lines, address components, attachment records. For associations where the child has independent meaning, RESTRICT plus an explicit archival or reassignment path is safer, because cascade makes it possible to destroy a large amount of data with a single-row delete.
How do soft deletes interact with foreign keys?
They defeat them. The parent row still physically exists, so the constraint is satisfied even though the entity is logically gone, and the database cannot enforce the rule you actually care about. The liveness check moves into application code, which means every query must remember it — a real cost that should be acknowledged when choosing soft deletes.
Do foreign keys cause deadlocks?
They participate in them. A child insert locks the referenced parent row, so two transactions inserting children of different parents while also touching the other parent can form a cycle — through locks neither statement mentions explicitly. The fix is the usual one: a consistent order for acquiring rows across all paths.
Is it ever right to omit foreign keys?
Across service boundaries you have no choice, and that is a genuine cost of splitting — referential integrity becomes an eventual-consistency problem with reconciliation. Within one database, omitting them for write throughput is a real but rarely-justified trade, and it should follow a measurement rather than an assumption.
Answers that lose the round
- Not indexing the referencing column, then being unable to explain why deletes are slow
- Using CASCADE for convenience on a relationship that is association rather than composition
- Cascading a bulk delete in one transaction and holding locks across millions of rows
- SET NULL on a column downstream code assumes is always populated
- Dropping foreign keys "for performance" and accumulating orphans silently
- Soft deletes plus foreign keys, believing the database still enforces liveness
- Not realising child inserts lock the parent, then mis-diagnosing the resulting deadlock
FAQ
Does MySQL behave the same way?
Broadly, with one notable difference: InnoDB automatically creates an index on the referencing column if one does not exist, so the slow-delete problem is less common there. The cascade and locking considerations still apply.
Should the foreign key be on the primary key or a natural key?
A stable surrogate key is the safer default, because referencing a natural key means a change to that value has to propagate to every referencing row. ON UPDATE CASCADE can do it, but you have made a routine data correction into a large write.
How do I add a foreign key to an existing large table?
Two steps, like other constraints: add it NOT VALID so it applies to new writes with only a brief lock, fix or remove any existing violations, then VALIDATE CONSTRAINT under a weaker lock. Index the referencing column first, or the validation scan itself will be slow.
What about circular references between two tables?
They need deferrable constraints so both rows can be inserted within one transaction before the checks run at commit. It works, but it is worth asking first whether the cycle reflects a modelling problem — a nullable column on one side, or a join table, is frequently the better answer.