Describe what a relational engine actually does at write time to enforce a foreign key: what work happens when a child row is inserted or its referencing columns are updated, and what happens when the parent row is deleted or its key value changes.
answer
- internal triggers, checked inside the txn
- child write → probe parent unique index
- parent delete/key update → search every child table
- unchanged FK columns → check skipped
- cost is per row, not per statement
basics
~20 sChild INSERT/UPDATE triggers a lookup in the parent's unique index for the referenced value; if absent, the statement fails. Parent DELETE or key UPDATE triggers a reverse search of the child table for referencing rows; if any exist, the operation is refused. Both run inside the writing transaction.
solid answer
~60 sEnforcement is two symmetric checks, implemented internally as system-level triggers fired by the write. **Child side.** On INSERT, or on an UPDATE that changes the referencing columns, the engine probes the parent's primary/unique index for the new value. Found means proceed; not found means raise a referential-integrity error and roll the statement back. If the value is NULL the probe is skipped. Only affected columns matter — an UPDATE that leaves the referencing columns untouched normally skips the check entirely. **Parent side.** On DELETE of a parent row, or an UPDATE that changes the referenced key value, the engine searches each referencing table for children with that value. Existence of even one child means the operation is refused (or triggers whatever referential action was declared). This search is the expensive half: it hits the child table, and without an index on the child's referencing columns it degrades to a full scan per parent row touched. Both checks run inside the transaction and, by default, at the end of each statement, so the invariant is intact at every statement boundary.
go deeper
Name the two checks in plain terms: inserting a child looks up the parent; deleting a parent looks for children.
Add the access paths — parent probe uses the guaranteed unique index, child search often has none — and note that unchanged referencing columns skip the check.
Talk about per-row cost on bulk statements, why the check uses a current read plus lock rather than the transaction snapshot, and how purge jobs are shaped around the asymmetry.
Reason about where the enforcement cost lands across the workload: write amplification per referencing table, the effect on archival and partition drop strategies, and when the invariant is worth its price.
## Enforcement is a lookup, not magic A foreign key is implemented as internal, system-owned triggers (Postgres literally installs them; InnoDB, Oracle and SQL Server use equivalent internal machinery). Every write that could break the invariant fires a check, and the check is an ordinary index probe or table search executed inside the writing transaction. ## Child-side check: does a parent exist? Fired by INSERT into the child table, and by UPDATE of the child's referencing columns. 1. If any referencing column is NULL, the default MATCH SIMPLE rule skips the check. 2. Otherwise the engine probes the referenced table's primary key or unique index for the new value. That index is guaranteed to exist, because the standard requires the referenced columns to be a key. 3. Found → the write proceeds. Not found → a referential-integrity error and the statement is undone. This is cheap: one index probe per affected row, and the parent's key pages are typically hot in the buffer pool. It also implies that an UPDATE which touches other columns does not pay the cost — engines skip the check when the referencing columns are unchanged. ## Parent-side check: are there children? Fired by DELETE of a parent row, or UPDATE of the referenced key value. The engine must search *every* referencing table for rows carrying the old key value. Semantically it is `SELECT 1 FROM child WHERE child.fk_col = :old_value` for each foreign key that points at this table. If any row is found, the write is rejected because completing it would leave orphans. This is the expensive direction, and the reason is access path. The child's referencing columns are *not* indexed automatically by most engines. Without an index, that probe becomes a full scan of the child table — per deleted parent row, per referencing table. Deleting a thousand parents from a table referenced by three large children can turn into thousands of full scans and an apparently hung DELETE. ## When the checks run By default the checks are immediate: they complete before the statement returns, so after every statement the database is referentially consistent. (Some engines allow deferring the check to commit, which is a distinct topic.) Because the check happens inside the transaction, it observes the transaction's own uncommitted work: a transaction can insert a parent and then its children in the same transaction and both checks pass. Under snapshot-based concurrency the check must not be done against a stale snapshot — engines therefore read the parent row with a current-read plus a lock, rather than the transaction's ordinary MVCC snapshot, so a concurrently committed parent DELETE cannot slip past the check. ## Self-referencing foreign keys When a table references its own key — `employees.manager_id REFERENCES employees(id)`, or a tree's `parent_id` — the same two checks apply, both against the same table. Practical consequences: the root row must have a NULL referent (or reference itself, if the engine allows the self-row to satisfy its own check within the statement); inserts must be ordered parents-before-children unless the check is deferred; a multi-row INSERT whose rows reference each other may fail on ordering; and deleting a subtree must proceed leaves-upward. Bulk-loading a hierarchy typically means loading with the constraint deferred or not-yet-validated, then validating once. ## Multi-row statements and cost Costs are per affected row, not per statement. A 100k-row INSERT does 100k parent probes; a 100k-row parent DELETE does 100k child searches per referencing table. That asymmetry — cheap probe one way, potentially unbounded search the other — is the single most useful thing to remember about foreign key enforcement, and it drives both index design on the child side and how archival/purge jobs are written.
- Why is deleting a parent row usually far more expensive than inserting a child row?The child-side check is a single probe of the parent's primary or unique index, which always exists. The parent-side check must search each referencing table for children carrying that key, and those referencing columns are usually not indexed, so the search degrades to a full table scan — repeated for every parent row deleted and every referencing table.
- A table has a self-referencing foreign key such as parent_id pointing at its own id. What does that change about loading data?Rows must arrive in dependency order, parents before children, or the child-side probe fails. Root rows need a NULL parent_id. Deletes go the other way, leaves first. For bulk loads the practical approach is to load with the constraint deferred or created unvalidated and validate once at the end, rather than sorting the input by depth.
saying these in an interview costs you the question
- Saying enforcement happens only on INSERT into the child table
- Believing every UPDATE of a child row revalidates the foreign key even when the referencing columns did not change
- Claiming the parent-side check is free because 'the database just knows'
- Assuming the check is asynchronous or eventual rather than inside the transaction
- Thinking a transaction cannot insert a parent and its children in the same transaction