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?

## 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.