CONCEPT Cited by 1 source

Foreign key constraint¶

Definition¶

A foreign key constraint is a SQL-schema declaration that a column (or ordered tuple of columns) on a child table must match a corresponding column tuple on a parent table — or be NULL — at every moment. The database engine is responsible for enforcement: rejecting writes that would leave the child row unmatched, and/or cascading parent-side changes to the child rows that depend on them.

The MySQL-specific rule set¶

From Shlomi Noach + Manan Gupta (PlanetScale, 2023-12-05, sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints), InnoDB enforces these rules when FOREIGN_KEY_CHECKS=1:

A FK is a relationship between a parent table and a child table. A table may reference itself (self-reference).
The referenced (parent) table must exist.
The referenced columns on the parent must exist.
The referenced columns on the parent must be indexed in-order. "There has to be an index covering the referenced columns in the same order they're referenced by the constraint." The index may cover additional columns beyond the referenced ones.
The child columns must match the parent columns in count and data type. INT on child may not reference BIGINT on parent; interestingly, VARCHAR(32) on child is allowed to reference VARCHAR(64) on parent (MySQL tolerates the length asymmetry on character columns).

The referential-action choices¶

Per-FK ON DELETE and ON UPDATE actions:

Action	Semantics
`NO ACTION` / `RESTRICT`	Reject the parent change if any matching child row exists. No cascade.
`CASCADE`	Propagate the parent's delete/update to every matching child row.
`SET NULL`	Set the child's FK column(s) to `NULL` on parent delete/update.
`SET DEFAULT`	Rarely used in MySQL; parser accepts but InnoDB treats as `NO ACTION`.

Why this matters architecturally¶

The CASCADE and SET NULL variants are the ones that actually write to the child in response to a parent-side change. In InnoDB, those child-writes happen inside the storage engine, invisibly to the server layer — which means they never land in the binlog. See concepts/innodb-silent-cascade-in-binlog for the load-bearing consequence: any CDC / replication / snapshot-plus-catchup-tool that tails the binlog is missing data for cascaded child changes, and must either work around the gap or re-implement FK logic above MySQL.

MySQL's storage-engine history¶

From the post: historically "The MySQL engines did not support foreign key constraints. It was a 3rd party pluggable engine named InnoDB that fast became the engine of choice". MySQL AB later began a server-level FK effort but dropped it when InnoDB became MySQL's default engine (both under Oracle ownership). Consequence: FK is not a server-layer primitive — it's an InnoDB primitive. Switch storage engines and the constraint machinery disappears.

`FOREIGN_KEY_CHECKS` — the enforcement switch¶

Session-level MySQL variable:

SET FOREIGN_KEY_CHECKS=0; -- skip FK validation for this session
-- ... bulk load / dump restore / schema rebuild ...
SET FOREIGN_KEY_CHECKS=1;

Useful during bulk imports, dump restores, and schema rebuilds that can't always obey referential order. Turning it off turns off all FK work — validation, cascading, everything. Applications normally run with =1.

Seen in¶

sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints — canonical wiki home for the concept. Noach + Gupta ship FK-constraint support in Vitess and PlanetScale after ~1 year of engineering and are the canonical Vitess-maintainer voice on the MySQL FK rule set, the enforcement model, and why the ecosystem implications are non-trivial (see concepts/innodb-silent-cascade-in-binlog and concepts/vitess-foreign-key-enforcement). The post also motivates canonical wiki framing of FK-constraint names as non-idempotent under shadow-table online-DDL: constraint names must be unique to the schema (ANSI SQL), and every deployment produces a new synthetic name — a subtle deploy-visibility issue surfaced by the post.