Foreign key design

A foreign key is a constraint that says a column (or group of columns) in a child table must match a key that exists in a parent table. Designing one well means choosing what it references, what happens when the parent is deleted, how the child side is indexed and what it costs in locks, and recognising the systems where the guarantee is not worth its price.

Picture it first: the society’s parking stickers

A housing society issues parking stickers, and every sticker names a flat. The secretary keeps two registers: flats, and stickers.

• No orphans. Before issuing a sticker for B-204, the secretary checks that B-204 exists in the flats register.
• Demolition rules. When a flat is merged into its neighbour, the society must decide what happens to its stickers: refuse the merger until they are returned, cancel them all automatically, or keep them but mark them unassigned.
• Finding the stickers. The sticker register is sorted by sticker number. To find B-204’s stickers, the secretary reads all 2,000 entries, unless someone keeps a second list sorted by flat.
• A finger on the page. While writing a sticker for B-204, the secretary keeps a finger on B-204’s line so nobody strikes it out mid-way. Someone can still repaint B-204’s nameplate; nobody can demolish it.

Map it back: the check is the foreign key, the demolition rule is the referential action, the second list is an index on the child column, and the finger is the lock an FK check takes on the parent row.

What a foreign key guarantees

CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (customer_id),
  merchant_id bigint NOT NULL REFERENCES merchants (merchant_id)
);

Referential integrity: every non-null customer_id in orders exists in customers, after every statement, for every writer. A NULL escapes the check, so write NOT NULL when the link is mandatory.

What it may reference. In PostgreSQL the target must be a primary key, a non-deferrable unique constraint, or a non-partial unique index, so the parent side always has an index for the lookup. InnoDB needs an index on the referenced columns too, and MySQL 8.4 deprecates its old extension that allowed non-unique targets. Types must match: an int child pointing at a bigint parent is a schema smell even where it is allowed.

Composite foreign keys

A foreign key can span several columns, and then it enforces a rule no single-column key can. The normalization article noted that “every dish on an order comes from that order’s restaurant” spans three tables. Two composite keys enforce it:

ALTER TABLE orders ADD UNIQUE (order_id, merchant_id);   -- targets for the
ALTER TABLE dishes ADD UNIQUE (dish_id, merchant_id);    -- composite keys below

CREATE TABLE order_lines (
  order_id    bigint NOT NULL,
  line_no     int    NOT NULL,
  merchant_id bigint NOT NULL,
  dish_id     bigint NOT NULL,
  qty         int    NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, line_no),
  FOREIGN KEY (order_id, merchant_id) REFERENCES orders (order_id, merchant_id)
    ON DELETE CASCADE,
  FOREIGN KEY (dish_id, merchant_id)  REFERENCES dishes (dish_id, merchant_id)
);

A line carries one merchant_id, which must match both its order’s and its dish’s, so a dish from another restaurant cannot be attached. The extra UNIQUE constraints exist only to be targets; they cost an index each.

NULLs in composite keys. Under the default MATCH SIMPLE, a row escapes the check if any referencing column is NULL. MATCH FULL lets a row escape only if all are NULL, so a mix fails. PostgreSQL implements both (MATCH PARTIAL is not implemented). MySQL enforces no MATCH clause; writing one makes InnoDB ignore your ON DELETE and ON UPDATE clauses, so leave it out. The simplest fix for both engines is NOT NULL on every column that must always point somewhere.

Self-referencing keys

Trees point at themselves: categories.parent_id REFERENCES categories, or employees.manager_id REFERENCES employees, with NULL at the root. MySQL adds limits: cascades nest at most 15 levels deep, a self-referential ON UPDATE CASCADE acts like RESTRICT, and because checks are immediate a row that references itself cannot be deleted. Use NULL, not a self-pointer, for the root.

Referential actions: what happens on delete

The ON DELETE clause decides what a parent delete does to rows that still point at it (ON UPDATE does the same when a referenced key changes, which is rare with surrogate keys). PostgreSQL runs these actions inside the deleting statement, even when the check itself is deferred.

PostgreSQL’s docs give the rule of thumb. If the child is a component of the parent and cannot exist alone, CASCADE. If they are independent things, RESTRICT or NO ACTION, and an application that wants both gone deletes both explicitly. If the link is optional information, SET NULL. So order_lines cascade with orders, orders restrict on customers (orders are financial records: anonymize a departing customer instead of deleting them), and support_tickets.order_id sets NULL.

The column-list form fixes a multi-tenant trap. With FOREIGN KEY (tenant_id, rider_id) REFERENCES riders ON DELETE SET NULL, deleting a rider would also null tenant_id, which is part of the child’s primary key and fails. PostgreSQL 15 added ON DELETE SET NULL (rider_id) to clear only that column.

The blast radius of CASCADE

A merchant leaves the platform: DELETE FROM merchants WHERE merchant_id = 42, with CASCADE down to 2M orders and their 6M lines. That one statement deletes 2M + 6M = 8M rows in one transaction. At a rough 100 bytes of write-ahead log per deleted row, that is 8M × 100 B ≈ 800 MB of WAL for replicas to replay, row locks on all 8M rows until commit, and a long transaction that holds back MVCC cleanup.

MySQL adds two surprises. Its manual says cascaded actions do not activate triggers, so audit triggers on order_lines never see those deletes. And Vitess’s docs warn that MySQL does not write the cascaded child changes to the binary log, because InnoDB performs them internally, so binlog readers such as change data capture pipelines miss them.

Index the child side

A foreign key needs an index on the parent key so each child insert can find its parent, and every engine guarantees one. The child side is where engines differ:

• PostgreSQL does not index the referencing columns. Its docs explain why you usually should: deleting a parent row, or updating its key, must scan the child table for rows matching the old value. It leaves the choice to you because the index is not always needed.
• InnoDB requires one. The child needs an index whose first columns are the FK columns in order, and MySQL creates it automatically if missing.

Put numbers on the PostgreSQL case. order_lines has 200M rows of about 100 bytes: 200M × 100 B = 20 GB. Each order deleted runs one query to find its lines (to cascade, or to check none remain). Without an index that query is a full scan: 20 GB ÷ 500 MB/s ≈ 40 seconds per deleted order. A cleanup job removing 1,000 test orders takes 1,000 × 40 s = 40,000 s, about 11 hours. With an index, each lookup descends a B-tree of about four levels and finishes in around a millisecond: about 1 second for all 1,000.

The same scan runs when you update a parent key, and with CASCADE or SET NULL it is followed by the writes. Skipping the index is reasonable only when parent rows are never deleted or re-keyed and nobody queries children by parent: a currency_code pointing at a ten-row currencies table, say.

Column order matters. The index must lead with the FK columns, in any order among themselves. In order_lines, the primary key (order_id, line_no) already serves the order FK, but the dish FK needs its own index starting with dish_id and merchant_id. A junction table product_categories (product_id, category_id) is covered for products by its primary key and needs (category_id) for the other side.

To list PostgreSQL foreign keys that have no usable index (no valid, non-partial index whose leading columns are exactly the FK columns):

SELECT c.conrelid::regclass  AS child_table,
       c.conname             AS fk_name,
       c.confrelid::regclass AS parent_table
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
        SELECT 1
        FROM pg_index i
        WHERE i.indrelid = c.conrelid
          AND i.indisvalid
          AND i.indpred IS NULL
          AND (string_to_array(i.indkey::text, ' ')::int2[])[1:cardinality(c.conkey)]
              @> c.conkey
      );

indkey prints as space-separated column numbers (1 3), so the query turns it into an array, takes its first N entries and checks they contain all N FK columns. Build missing indexes with CREATE INDEX CONCURRENTLY so writes continue (database indexes).

The locks a foreign key check takes

To check a new child row, the database reads the parent row and locks it until the child’s transaction ends, so nobody can delete the parent in between. The lock’s strength is what matters.

PostgreSQL takes FOR KEY SHARE on the parent (since 9.3, when the weaker lock modes were added for exactly this). It blocks a DELETE of the parent and any UPDATE that changes a column with a unique index usable by a foreign key, because those take FOR UPDATE. It does not block other updates, which take FOR NO KEY UPDATE. So a child insert and an update of the order’s status or total proceed side by side (optimistic vs pessimistic locking has the full conflict table).

InnoDB sets shared record locks on the parent records it examines, and keeps them until commit, even when the check fails. When the FK references the parent’s primary key (the usual case), that shared lock sits on the parent row itself, and any UPDATE of that row needs an exclusive lock. So in MySQL, an in-flight child insert blocks every update to its parent row.

That difference turns a common pattern into a deadlock. Two requests add a line to the same order, then bump orders.total:

The MySQL fix is lock ordering: touch the parent first. Update the order’s total (or SELECT … FOR UPDATE it) before inserting the line, so every transaction queues on the parent’s exclusive lock instead of collecting shared locks it later needs to upgrade. The same order costs nothing in PostgreSQL. Keep a retry loop for deadlock errors (1213 in MySQL, 40P01 in PostgreSQL) anyway.

Hot parents

Every child insert locks its parent, so a parent with many concurrent children becomes a hot row. A flash sale sends 2,000 orders a second to one cloud kitchen, each in a transaction of about 20 ms. By Little’s law, 2,000 × 0.02 s = 40 transactions hold a lock on that merchant row at any moment.

• In InnoDB, any update to the merchant row (its rating, is_open, a last_order_at) needs an exclusive lock and must wait for a moment when none of those 40 shared locks is held. Under steady load that moment may not come before the lock wait timeout.
• In PostgreSQL, ordinary updates don’t wait, but when more than one transaction locks a row at once, the set of lockers is stored as a MultiXact in pg_multixact, extra I/O that vacuum must later age and clean up.

The fixes: keep frequently updated fields off hot parent rows (a separate merchant_stats table), keep child transactions short, and for the very hottest relationships, consider checking the reference in the application instead. The general hot-row playbook is in contention and double booking.

Modelling patterns

Polymorphic associations

A comments(target_type, target_id) table, where the target may be a restaurant, a dish or an order, cannot have a foreign key: one column cannot reference three tables. Deleting a dish leaves its comments pointing at nothing. Three designs restore real keys:

Exclusive arc: one nullable foreign key per target type, and a CHECK that exactly one is set:

CREATE TABLE comments (
  comment_id    bigint PRIMARY KEY,
  restaurant_id bigint REFERENCES restaurants ON DELETE CASCADE,
  dish_id       bigint REFERENCES dishes      ON DELETE CASCADE,
  order_id      bigint REFERENCES orders      ON DELETE CASCADE,
  body          text   NOT NULL,
  CONSTRAINT exactly_one_target
    CHECK (num_nonnulls(restaurant_id, dish_id, order_id) = 1)
);
CREATE INDEX ON comments (restaurant_id);   -- and one each for dish_id, order_id

You gain real keys, per-target actions and plain joins. You pay a column and an index per target type, and a migration for each new type. MySQL has no num_nonnulls, but (restaurant_id IS NOT NULL) + (dish_id IS NOT NULL) + (order_id IS NOT NULL) = 1 works; it also rejects a CHECK on columns that a referential action needs (error 3823), so drop the CASCADEs there and delete comments explicitly.

Supertype table: every commentable thing first gets a row in commentables, carries its ID, and comments reference that one table. You gain a single foreign key that scales to new target types. You pay an extra insert per target and a join to learn what a comment is attached to.

A join table per target (dish_comments(dish_id, comment_id) and so on) keeps comments generic and moves the keys into small link tables, at the cost of one table per target type.

Soft deletes

With deleted_at instead of DELETE, the parent row still exists, so the foreign key is still satisfied and no referential action fires. Comments on a “deleted” dish stay put, and nothing stops a new order line from referencing it. Filter on deleted_at IS NULL when attaching children, soft-delete children yourself, and remember the eventual hard purge will run every cascade at once, so batch it. A partial unique index on email WHERE deleted_at IS NULL keeps live rows unique, but a foreign key cannot target a partial index, so references keep using the surrogate key.

Tenant-scoped keys

In a multi-tenant database, a bug that passes another tenant’s ID is a data leak. Include tenant_id in the keys and the foreign key catches it:

CREATE TABLE projects (
  tenant_id  bigint NOT NULL,
  project_id bigint NOT NULL,
  PRIMARY KEY (tenant_id, project_id)
);

CREATE TABLE tasks (
  tenant_id  bigint NOT NULL,
  task_id    bigint NOT NULL,
  project_id bigint NOT NULL,
  PRIMARY KEY (tenant_id, task_id),
  FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, project_id)
);

A task in tenant 7 can point only at tenant 7’s projects; tenant 9’s project ID fails with 23503. The NOT NULLs matter: under MATCH SIMPLE, a NULL tenant_id would let the row skip the check. The keys lead with tenant_id, matching tenant-filtered queries. The isolation models around this are in multi-tenant data.

Circular references

A merchant has a mandatory primary outlet, and every outlet belongs to a merchant. With both columns NOT NULL, neither row can go in first, unless one key waits for commit:

ALTER TABLE merchants
  ADD CONSTRAINT merchants_primary_outlet_fk
  FOREIGN KEY (primary_outlet_id) REFERENCES outlets
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
INSERT INTO merchants (merchant_id, name, primary_outlet_id) VALUES (42, 'Dosa Corner', 901);
INSERT INTO outlets (outlet_id, merchant_id, area) VALUES (901, 42, 'HSR Layout');
COMMIT;   -- the deferred key is checked here

Making it (merchant_id, primary_outlet_id) against a UNIQUE (merchant_id, outlet_id) on outlets also guarantees the primary outlet is the merchant’s own. MySQL cannot defer, so there the column must be nullable and filled after the outlet exists, or the link moves to a merchant_primary_outlet table. Database constraints covers deferral in general.

Lookup tables

status text REFERENCES order_statuses keeps the readable code in the row, so reads need no join, and adding a status is an INSERT. The cost: every order insert checks and share-locks the 'placed' row, which makes it the hottest parent in the database. That is harmless while nobody updates status rows, which is why lookup tables should be effectively read-only.

Adding foreign keys to big tables

A plain ALTER TABLE … ADD FOREIGN KEY in PostgreSQL takes a SHARE ROW EXCLUSIVE lock on both tables, which blocks inserts, updates and deletes on each, then checks every child row while holding it. Split it, as with other constraints:

CREATE INDEX CONCURRENTLY order_lines_dish_idx ON order_lines (dish_id, merchant_id);

ALTER TABLE order_lines
  ADD CONSTRAINT order_lines_dish_fk FOREIGN KEY (dish_id, merchant_id)
  REFERENCES dishes (dish_id, merchant_id) NOT VALID;     -- brief lock, no scan

ALTER TABLE order_lines VALIDATE CONSTRAINT order_lines_dish_fk;

From the moment NOT VALID commits, new and updated child rows are checked. VALIDATE then scans existing rows under SHARE UPDATE EXCLUSIVE on the child and ROW SHARE on the parent, neither of which blocks normal reads and writes. If it finds orphans it fails, and you clean them up and retry while no new ones get in. The details of the lock levels are in database constraints.

In MySQL 8.4, adding a foreign key runs in place, with writes allowed, only when foreign_key_checks is off, and then existing rows are not checked; with checks on, the table is copied. So run an orphan query first, then add the key with checks off.

Bulk loads and foreign_key_checks

Load parents before children, or in PostgreSQL load inside one transaction with deferrable keys set to DEFERRED, or add keys after the load with NOT VALID and VALIDATE. MySQL’s shortcut is SET foreign_key_checks = 0, which mysqldump puts in its dumps so tables can load in any order. Its danger is in the manual: turning checks back on does not scan the data, so any orphan loaded meanwhile stays forever. With checks off you can also drop a table that others still reference.

After any unchecked load, and on a schedule wherever keys are not enforced, look for orphans with an anti-join:

SELECT l.order_id, count(*) AS orphan_lines
FROM order_lines l
LEFT JOIN orders o ON o.order_id = l.order_id
WHERE o.order_id IS NULL          -- for a nullable FK, also: AND l.order_id IS NOT NULL
GROUP BY l.order_id
LIMIT 100;

Going deeper: when large systems drop foreign keys

Many large MySQL installations run with few or no foreign keys. It is a trade, not an oversight:

• Online schema changes. Tools that build a shadow copy of a table and swap it in don’t mix well with constraints pointing at the original. GitHub’s gh-ost lists foreign keys as unsupported, and Vitess warns that its online DDL doesn’t work well with them. If every ALTER on a 2 TB table must go through such a tool, FKs stand in the way of every schema change.
• Sharding. A foreign key works only inside one database. Vitess’s default mode ignores MySQL foreign keys, and you must place related rows on the same shard (a shared vindex). Cascaded changes are missing from the binary log, which can break binlog-driven workflows such as MoveTables. Its managed mode is experimental and shard-scoped, and a disallow mode bans foreign keys outright. See sharding a relational database.
• Service boundaries. Once payments live in their own service and database, payments.order_id is just an ID. Integrity becomes events, the transactional outbox and reconciliation (monolith vs microservices).
• Write cost and locks. Every child write does a parent lookup and takes a parent lock, which hurts most on the hot parents above.

Dropping them moves the work, it doesn’t remove it. You then need existence checks in the write path, deletes in a defined order (children first, in batches), scheduled orphan queries like the anti-join above, ideally on a replica, and readers that tolerate a dangling ID (“this dish is no longer available”). PostgreSQL 18 offers a middle ground for documentation: a NOT ENFORCED foreign key records the relationship without checking it, and the planner may still use it where that cannot change results.

Cheat sheet

How to say it in the interview

“Every relationship inside one database gets a foreign key unless I have a measured reason not to. Order lines cascade with their order, orders restrict on customers, and optional links like a ticket’s order set NULL. In PostgreSQL I index every child column, because otherwise deleting a parent scans the whole child table. I know an FK check locks the parent row: in PostgreSQL it’s KEY SHARE, which doesn’t block normal updates, but InnoDB’s shared lock does, so I update the parent before inserting children to avoid deadlocks. On big tables I add keys as NOT VALID and validate later. Across shards and services I drop them and rely on IDs, events and scheduled orphan checks.”

Should every relationship have a foreign key? Inside one database, by default yes: it is cheap, documents the schema and binds every writer. Drop it deliberately when online migration tools can’t handle it, when rows live on different shards or services, or when a hot parent makes the locks too costly, and replace it with application checks and orphan audits.

Why is my DELETE on the parent so slow? Almost always a missing index on the child’s FK column. PostgreSQL doesn’t create one, so each deleted parent row triggers a full scan of the child table to find (or cascade to) its children. Create the index concurrently. EXPLAIN ANALYZE on the delete (inside a transaction you roll back) reports time per trigger, and foreign keys run as internal triggers, so the cost shows up there.

How do you handle foreign keys when you shard? Choose a shard key that keeps related rows together (orders and their lines by merchant_id), and keep FKs inside each shard. References across shards become plain IDs, checked by the application and audited by background anti-joins; global reference data is replicated to every shard.

CASCADE or RESTRICT for orders on a customer? RESTRICT. Orders are financial records with a life of their own, so deleting a customer who has orders should fail loudly. Anonymize or deactivate the customer instead.

Where to go next

• Database constraints: NOT NULL, CHECK, UNIQUE and EXCLUDE, deferral, and adding constraints online.
• Database normalization: where the keys that foreign keys point at come from.
• Optimistic vs pessimistic locking: row lock modes, deadlocks and hot rows.
• Database indexes: the B-trees that make child lookups cheap, and their write cost.
• Multi-tenant data: tenant-scoped keys and isolation models.
• Sharding a relational database: choosing shard keys that keep related rows together.
• Change data capture: moving changes between services, and why cascades can hide from the binlog.
• Online schema and data migrations: changing big tables without downtime.

References and further reading

1. PostgreSQL documentation: Constraints, foreign keys (section 5.5.5)postgresql.orgWhat a foreign key may reference, NO ACTION versus RESTRICT, CASCADE and SET NULL column lists, MATCH FULL, and why the referencing columns are not indexed for you.
2. PostgreSQL documentation: CREATE TABLEpostgresql.orgMATCH SIMPLE and MATCH FULL, each referential action, actions running even when the check is deferred, DEFERRABLE, NOT ENFORCED, and the lock taken on the referenced table.
3. PostgreSQL documentation: ALTER TABLEpostgresql.orgADD FOREIGN KEY takes SHARE ROW EXCLUSIVE on both tables; NOT VALID, and VALIDATE CONSTRAINT under SHARE UPDATE EXCLUSIVE plus ROW SHARE on the parent.
4. PostgreSQL documentation: Explicit locking, row-level lockspostgresql.orgFOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE, which statements take each, and their conflicts.
5. PostgreSQL 9.3 release notespostgresql.orgForeign-key checks take KEY SHARE locks and non-key updates take NO KEY UPDATE, so ordinary parent updates no longer block child inserts.
6. MySQL 8.4 Reference Manual: FOREIGN KEY constraintsdev.mysql.comInnoDB requires and auto-creates an index on the FK columns, NO ACTION equals RESTRICT, SET DEFAULT is rejected, cascades skip triggers, and foreign_key_checks does not recheck rows when re-enabled.
7. MySQL 8.4 Reference Manual: FOREIGN KEY constraint differencesdev.mysql.comRow-by-row, non-deferred checks with shared locks; MATCH is ignored; inline REFERENCES does nothing; cascades nest at most 15 levels.
8. MySQL 8.4 Reference Manual: Locks set by different SQL statements in InnoDBdev.mysql.comAny statement that checks a foreign key sets shared record locks on the rows it looks at, even when the check fails.
9. gh-ost: requirements and limitationsgithub.comGitHub’s online schema migration tool does not support foreign key constraints.
10. Vitess documentation: Foreign keysvitess.ioUnmanaged, managed and disallow modes; FK-related rows must share a shard; cascaded child changes are not in MySQL’s binary log.