When you INSERT a row into a child table that has a foreign key referencing a parent table, what extra work does the database do before that statement can succeed, and when does that extra work become a throughput bottleneck?
answer
- Probe parent unique index per row
- Key-share lock held to commit
- Hot parent row = queue of writers
- Unindexed child FK = full scan on parent delete
- Six FKs = six probes per insert
basics
~20 sFor every inserted row the engine looks up the referenced key in the parent's unique index to prove the parent exists, and takes a light lock on that parent row so it cannot be deleted or re-keyed before commit. It costs an extra index probe plus contention on hot parent rows.
solid answer
~60 sA foreign key is not free metadata — it is a per-row check executed inside the writing transaction. On each child INSERT (or an UPDATE that changes the FK columns) the engine: 1. Probes the parent's primary/unique index for the referenced value — an extra index descent, and often a row visit to confirm the parent version is visible. 2. Takes a **share-style lock** on that parent row (a key-share lock, or in some engines a shared row lock) so no concurrent transaction can delete it or change its key until the child transaction ends. So the cost is roughly one extra point lookup per FK per row, plus a lock entry held until commit. It bites when: many child rows point at the *same* parent row (a status or tenant lookup row) — every writer queues on the same parent lock; when a table has several FKs, multiplying probes; when the parent index is cold, making the probe random I/O; and when transactions touch parents in different orders, producing deadlocks.
code
sql · 6 lines-- conceptually executed per inserted child row:
SELECT 1 FROM orders WHERE id = :order_id FOR KEY SHARE;
-- almost never created automatically -- without it,
-- DELETE FROM orders WHERE id = ? scans order_line
CREATE INDEX ix_order_line_order_id ON order_line (order_id);go deeper
Know the shape: one lookup in the parent per inserted row to prove the parent exists, and it happens on every insert, not in the background.
Add the locking detail — a share-style lock on the parent row held to commit — and that an update skips the check when the FK columns are unchanged.
Diagnose it: hot parent rows serializing writers, unindexed child FK columns turning parent deletes into scans, and deadlocks arising from parent-lock ordering.
Frame the tradeoff — where the integrity budget is spent, when to keep constraints under high write rates versus batch-validate, and how FK topology (fan-in to hot lookup rows) shapes write scalability.
## What a foreign key actually is at write time A foreign key constraint says: every non-NULL value in the child's referencing columns must match an existing row in the parent's referenced columns. The referenced columns must be a primary key or a unique key — the SQL standard requires it, and engines rely on it, because the check has to be a *point lookup* against a unique structure, not a scan. Enforcement is not a background job. It runs synchronously, per row, inside the transaction that writes the child row, and it must hold until commit — otherwise a concurrent transaction could delete the parent between your check and your commit and leave an orphan. ## The insert path, step by step For `INSERT INTO order_line(order_id, ...) VALUES (42, ...)`: 1. The child row is written to the table and to each of the child's own indexes (normal write cost, nothing to do with the FK). 2. The engine executes an internal existence check equivalent to `SELECT 1 FROM orders WHERE id = 42 FOR KEY SHARE`. That is a descent of the parent's unique index (root → branch → leaf), and usually a fetch of the parent row itself to check visibility under MVCC. 3. It records a **shared / key-share lock** on that parent row. This lock blocks anyone who wants to delete the parent or change its key, but it does *not* block other inserters of sibling children, and in most modern engines it does not block ordinary updates of the parent's non-key columns. 4. The lock is held until the child transaction commits or rolls back. An UPDATE that does not touch the FK columns generally skips the check entirely — engines compare old and new key values first. ## Where the cost lands **Extra I/O.** One index descent plus a row visit per FK per row. If the parent table and its index are small and hot in the buffer pool (a 30-row `status` table), the probe is essentially free. If the parent is a billion-row table and the reference is random, the probe is a cache miss — and that miss is paid on the write path, where you were expecting only sequential log writes. **Lock contention.** This is the real bottleneck. Locks are on the *parent row*. If every child row references the same parent — a tenant row, a `currency` row, a `status = 'NEW'` row — then every concurrent writer touches that same lock entry. Share locks are compatible with each other, so simple inserts still proceed; the pain shows up when something wants an exclusive lock on that hot parent (an update to its key, a delete, or in older engine versions any update at all), at which point the whole write stream stalls behind it. **Deadlocks.** Two transactions inserting children of parents A and B in opposite orders can deadlock on the parent locks even though they never touch the same child row. This surprises people because their SQL never mentions the parent table. **Multiplication.** A table with six foreign keys pays six probes and takes six locks per inserted row. A bulk insert of a million rows takes six million probes. ## The other direction: parent deletes and updates The check runs both ways. Deleting a parent row (or updating its key) forces the engine to prove no child references it. That is a lookup on the *child* side, and it needs an index on the child's FK columns. Most engines do not create that index for you (a notable exception is one that indexes FK columns automatically). Without it, every parent delete becomes a full scan of every referencing child table — the single most common FK performance bug in the wild. Symptom: deleting one row from a small table takes seconds and gets slower as the child grows. ## Mitigations that keep the constraint - **Index the child's FK columns.** Non-negotiable if parents are ever deleted or re-keyed. - **Keep parent rows narrow and cached** so the probe is a buffer hit. - **Avoid pointing millions of children at one hot parent row** where you can; or accept share-lock traffic and never take exclusive locks on that row during peak load. - **Touch parents in a consistent order** across code paths to avoid deadlocks. - **Batch**, so the per-statement overhead amortizes; the per-row check remains but the round trips and log flushes do not. - **Defer the check to commit** where the engine supports deferrable constraints, if the problem is ordering within a transaction rather than raw cost — deferral moves the work, it does not remove it, and it costs memory to remember pending checks. - **Drop and re-validate around bulk loads**, where a single set-based anti-join replaces N point lookups. ## What to say in an interview "One extra unique-index probe per row plus a shared lock on the parent row, held to commit. It hurts on hot parent rows and when the child's FK column is unindexed, because then parent deletes scan the child." That sentence covers both directions and the two failure modes interviewers are fishing for.
- Does the engine create an index on the child's foreign key column for you?Almost never — most engines index only the parent's referenced key, which must already be a primary or unique key. The child side is your responsibility. Without it, every parent delete or key update triggers a full scan of the child table to prove no rows reference it, so the delete cost grows with the child's size.
- Two transactions insert unrelated child rows and deadlock. How is that possible when they never touch the same row?Both take shared locks on parent rows as part of FK enforcement. If transaction A locks parent 1 then parent 2, and B locks parent 2 then parent 1, they deadlock on the parent locks even though their child rows are disjoint. The fix is to touch parents in a consistent order, or to reduce transaction scope so fewer parents are held at once.
- Does an UPDATE on the child always re-run the foreign key check?No. Engines compare the old and new values of the referencing columns and skip the check when they are unchanged, which is the common case. Only an update that actually changes the FK columns pays for a fresh parent probe and lock.
It is like a bouncer checking a guest list entry and then keeping a finger on that name so nobody erases it while your guest is inside — cheap per guest, but everyone pointing at the same name crowds around one line of the list.
saying these in an interview costs you the question
- Saying foreign keys are 'just metadata' or 'checked by the optimizer', with no per-row runtime cost
- Assuming the database automatically indexes the child's foreign key column
- Claiming FK enforcement takes an exclusive lock on the parent row and therefore serializes all inserts
- Believing the check only runs on the child side, so parent deletes are free
- Saying deferring the constraint to commit removes the work rather than moving it