Foreign keys

A foreign key declares that child column values must exist in a parent row (or be null if allowed). The database enforces referential integrity on insert, update, and delete — relationships are not only application convention.

					CREATE TABLE order_items (
  id        BIGINT PRIMARY KEY,
  order_id  BIGINT NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
  product_id BIGINT NOT NULL REFERENCES products(id)
);
				

| `ON DELETE` / `ON UPDATE` | Effect | |-----------------------------|--------| | `RESTRICT` / `NO ACTION` | Block parent change if children exist | | `CASCADE` | Propagate delete/update to children | | `SET NULL` | Null out FK when parent goes away |

`CASCADE` simplifies cleanup but can surprise you with deep trees and lock duration. `RESTRICT` surfaces orphan risk at delete time. Bulk loads and blue/green migrations may temporarily drop FKs — document the window and re-verify integrity after.

On interviews: explain why FKs belong in the schema for money and inventory domains, and when teams defer them (sharded writes, legacy ETL) with compensating checks.

Common pitfalls: missing indexes on FK columns (slow joins and lock checks); `CASCADE` chains that delete more than intended; app-only integrity that race under concurrency; adding FK to a dirty table without a backfill audit.

The trade-off is balancing database-enforced correctness against migration flexibility and cross-shard references — use FKs where a broken link is a production incident, not a logging inconvenience.

Checklist:

• Name parent and child tables and cardinality.
• Pick `ON DELETE` behavior explicitly.
• Index every FK column used in joins.
• Describe migration/backfill before enabling the constraint.
• State what breaks if integrity is app-only.