🔗FOREIGN KEY

Child rows must point at a real parent

A FOREIGN KEY ties referencing column(s) in the child table to a PRIMARY KEY or UNIQUE key in the parent. Inserts/updates on the child fail if no parent row exists; deletes or updates on the parent follow ON DELETE / ON UPDATE rules you declare,RESTRICT (reject), CASCADE (propagate), SET NULL (nullable FKs only), etc.

SQL

FK with ON DELETE RESTRICT

CREATE TABLE core.aliquots (
  aliquot_id BIGSERIAL PRIMARY KEY,
  sample_id BIGINT NOT NULL REFERENCES core.samples (sample_id) ON DELETE RESTRICT,
  volume_ul NUMERIC(12,3) NOT NULL
);

✓Illustrative result set

status
CREATE TABLE

SQL

Delete parent blocked when children exist

DELETE FROM core.samples WHERE sample_id = 1001;

✓Illustrative result set

error
ERROR: update or delete on table "samples" violates RESTRICT on FK from aliquots

📛ON DELETE / ON UPDATE behaviors

Pick explicitly, defaults vary by engine and surprise operators.

Action	Behavior
RESTRICT / NO ACTION	Reject parent delete/update while matching children exist (subtle timing differences by dialect).
CASCADE	Delete or update matching child rows automatically, draw the cascade DAG before shipping.
SET NULL	Null out child FK columns, only valid when those columns are nullable by design.

FK column with no index on the child

Parent deletes and joins devolve into sequential scans on the child, p99 latency spikes when someone archives old samples.

Index every referencing column set used by FKs

Add CREATE INDEX ON aliquots (sample_id) (or covering variants) in the same migration as the FK.

Cross-database

FKs cannot span databases on most engines, use application-level sagas, outbox patterns, or warehouse post-validation instead.

Bulk load

Temporarily disabling FK checks speeds loads but risks orphan rows, re-enable and validate with a constraint scan before cutover; never leave checks off “for performance.”

⚡ Advanced / dialect note

Cyclic FK graphs (A→B→A) require careful migration ordering, sometimes deferred constraints or two-phase DDL (create tables, load, then add FKs) is the only sane path.

Documentation

Paste a one-line comment above each FK in migrations: business meaning, cascade blast radius, and who owns the parent lifecycle (archive vs hard delete).

TL;DR

A foreign key declares that child column values must reference existing parent key rows, enforcing referential integrity at insert/update/delete time. Options like ON DELETE CASCADE or SET NULL define what happens when a parent row disappears; pick explicitly, silent cascades can delete far more than operators expect. FKs usually require an index on the child side for join/delete performance.