skip to content

A team stores related rows across tables but declares no foreign key constraints, saying referential integrity is enforced in the application. What goes wrong, and when is skipping foreign keys actually defensible?

level: middleimportance: must knowfreq 60%

answer

  1. Check-then-insert is racy without locking the parent
  2. Enforced per code path, not per table
  3. Backfills, admin tools, other services bypass it
  4. Needs an index on the child referencing column
  5. Legit omission: cross-database or cross-shard parent

basics

~20 s

Application checks are racy and only cover the paths that implement them - backfills, admin tools and other services bypass them - so orphan rows accumulate silently. Foreign keys make the invariant unconditional. Skipping them is defensible mainly across separate datastores or shards, where the constraint is not expressible anyway.

solid answer

~1 min

An application check reads the parent, then writes the child. Between those two steps another transaction can delete the parent, so even a correct implementation has a race unless it locks the parent. Worse, the check exists only in the code paths that implement it: batch imports, data-fix scripts, support tooling, a second service and a future rewrite all write the same tables without it. Orphans then accumulate quietly, and the damage surfaces as missing joins, wrong counts and crashes on unexpected nulls, long after the write that caused it. A declared foreign key is enforced for every writer, participates in the transaction, and lets you state delete behaviour once (restrict, cascade, set null) instead of reimplementing it in each caller. Optimizers also use the constraint - for example to prove a join cannot drop or duplicate rows and eliminate it. **Costs:** an index lookup on the parent per child write, a lock or check on parent delete, an index required on the child column, and ordering constraints for bulk load. All are manageable: load with constraints disabled and revalidate afterwards, or add the constraint unvalidated and validate in the background. **Defensible omissions:** the parent lives in a different database, service or shard; the child is an append-only event log deliberately decoupled from parent lifetime; or an extreme write-rate hot path where the cost was measured, not assumed - and then only with a scheduled orphan check.

code

sql · 4 lines
sql
SELECT count(*)
FROM   order_line ol
LEFT   JOIN orders o ON o.order_id = ol.order_id
WHERE  o.order_id IS NULL;

go deeper

for a junior

Say what an orphan row is, why the database is the only place the rule holds for all writers, and what a foreign key gives you.

for a middle

Explain the race in check-then-insert, the delete-behaviour options, and the index the child column needs.

for a senior

Cover diagnosing existing orphans, adding constraints on a live table without a long lock, and the legitimate cross-service and bulk-load exceptions.

for a principal

Frame it as where invariants should live given multiple writers and service boundaries, and define the reconciliation policy for relationships that genuinely cannot be constrained.

## The claim under examination "We enforce it in the application" is the most common justification for an FK-less schema. It is worth taking seriously, because it is sometimes right - but the usual version of it is wrong for reasons that are structural, not stylistic. ## Why application enforcement fails **It is racy.** The canonical implementation is: `SELECT` the parent; if found, `INSERT` the child. Under any isolation level short of serialisable, another transaction can delete the parent between those statements, and the child is written anyway. Fixing the race requires locking the parent row for the duration - which is precisely what the foreign key does internally, only correctly and without a round trip. **It is per-path, not per-table.** The check lives in one service's write path. The tables are also written by data migrations, backfill scripts, support consoles, analytics reverse-ETL, a second service that grew later, and a developer with a psql session at 2am. Every one of these bypasses the check. The invariant you want is a property of the *data*, and only a constraint on the data can guarantee it. **Failures are silent and delayed.** An orphan does not raise an error when created; it raises one months later as a null-pointer failure, a report whose totals no longer reconcile, or an inner join that quietly drops rows a user is asking about. By then the causing write path is unidentifiable. **Delete semantics get reimplemented badly.** Without FKs, every caller that deletes a parent must know the full set of children and delete them in the right order. Miss one and you leak; get the order wrong and you fail halfway. A foreign key states the policy once - `ON DELETE RESTRICT`, `CASCADE` or `SET NULL` - and applies it to every deleter. **The optimizer loses information.** Declared constraints are facts a planner can use. Knowing a child's reference is mandatory and points at a unique parent lets it prove a join preserves cardinality, sometimes removing the join entirely, and improves row estimates. FK-less schemas forfeit that. ## The real costs FKs are not free, and a credible answer names the costs: - **Write-time lookup.** Each insert or update of the child performs a lookup on the parent key. It is an index probe, usually cached, but it is not zero. - **Locking on parent modification.** Deleting or updating a referenced key requires checking children, which needs an index on the child's referencing column - a missing one turns every parent delete into a full scan of the child. Forgetting this index is the single most common performance complaint blamed on foreign keys. - **Load ordering.** Bulk loads must insert parents before children, and restore tooling must respect dependency order. - **Cross-shard impossibility.** If the parent and child live in different physical databases, the engine cannot enforce anything. Most of these have standard answers. For bulk loads, drop or disable the constraints, load, then revalidate; or add the constraint in a non-validating mode where supported, so new writes are checked immediately while the historical backlog is validated separately without a long exclusive lock. ## When omission is legitimate 1. **Different datastore or service boundary.** If the parent lives in another service's database, the constraint is inexpressible. The honest design then adds a reconciliation job and an explicit policy for dangling references, rather than pretending the relationship is enforced. 2. **Sharded tables where the parent is on another shard.** Same reasoning. 3. **Deliberately decoupled append-only logs.** An audit or event table that must retain rows after the referenced entity is deleted genuinely should not have a restricting FK - though the correct expression of that is often a nullable reference with `ON DELETE SET NULL`, keeping the constraint where the row still points at a live parent. 4. **A measured hot path.** Occasionally the parent-lookup cost matters at extreme rates. This is defensible only with a measurement, and should come with a scheduled orphan detector so drift is visible. What is *not* legitimate: "the ORM handles it", "constraints slow us down" without numbers, "we will add them later" with no date, or "our tests cover it" - tests exercise the paths you wrote, and the problem is the paths you did not. ## Diagnosing and repairing an existing schema Finding orphans is a straightforward anti-join per suspected relationship. Repair is a judgement call per relationship: delete orphans if they are meaningless, reassign them to a placeholder parent if the rows carry value, or null the reference if the relationship is optional. Only once the data is clean can the constraint be added; the creation itself is the proof that the cleanup was complete, and from then on the invariant holds for every writer. Sequence it as: measure orphans, clean, add the constraint unvalidated so new writes are protected immediately, validate the history in the background, then add the missing index on the child column if it is not already there.

  • Why is checking that the parent exists before inserting the child not equivalent to a foreign key, even in correct application code?
    The check and the insert are separate statements, so another transaction can delete the parent in between and the child is written anyway. Closing that window requires holding a lock on the parent row for the duration of the write, which is what the constraint does internally. On top of the race, the check only protects the code path that implements it, while the constraint protects the table against every writer.
  • You add a foreign key and parent deletes suddenly become very slow. What is the likely cause?
    There is probably no index on the child's referencing column. To delete or update a referenced parent key the engine must find the referring child rows, and without an index that means scanning the child table for every parent row deleted. Creating the index on the child column restores normal performance; this is the most common performance complaint wrongly attributed to foreign keys themselves.

saying these in an interview costs you the question

  • Claiming an application-level existence check is equivalent to a constraint
  • Asserting foreign keys are always too slow without any measurement
  • Forgetting that the child's referencing column needs its own index
  • Believing an ORM's association mapping enforces referential integrity in the database
  • Treating 'we will add them later' as a plan with no cleanup or validation step

context

resolving links…