Skip to content

CONCEPT Cited by 1 source

Vitess foreign-key enforcement

Definition

Vitess foreign-key enforcement is the layer in Vitess that takes over from InnoDB for any DML that interacts with a foreign-key constraint with a cascading action. Instead of letting the underlying MySQL server enforce the FK natively, VTGate analyses the statement against the schema's FK graph and issues the explicit child-first / parent-second operations (plus locking) required to record every cascaded child change in the binlog — which MySQL would otherwise hide inside InnoDB (see concepts/innodb-silent-cascade-in-binlog).

Why this exists

MySQL/InnoDB's native FK support has one architectural property that is incompatible with everything above it in Vitess that depends on the binlog: cascaded child writes never reach the binlog. Reprising the list of dependent subsystems:

  • VReplication (data motion, resharding, online DDL, online imports, CDC).
  • Replication-based Online DDL (shadow tables rely on binlog tail for catch-up).
  • VStream CDC consumers (Debezium, Airbyte, PlanetScale Connect, Fivetran).
  • Any external replication or analytics consumer.

PlanetScale chose Vitess owns FK logic over patch InnoDB (see concepts/innodb-silent-cascade-in-binlog for the four-reason trade-off).

What cases VTGate actually intercepts

ON [DELETE|UPDATE] RESTRICT (aka NO ACTION)

Delegate to MySQL. No cascade, so no binlog hole. MySQL's native enforcement rejects the parent write if any child row would be orphaned. VTGate stays out of the way. Canonical fastpath.

ON DELETE CASCADE

VTGate orchestrates explicitly via patterns/application-level-cascade-orchestration:

  1. SELECT col FROM parent WHERE id=7 FOR UPDATE — lock the parent rows and read their FK column values.
  2. DELETE FROM child WHERE col IN (<parent_values>) — binlog the child-row deletes explicitly (the step InnoDB would skip).
  3. Recurse for grandchildren if present.
  4. DELETE FROM parent WHERE id=7 — InnoDB sees no matching children, nothing to cascade.

All four steps in a single transaction. VTGate acquires more locks than MySQL would; it also emits more binlog events — one per child row explicitly, instead of just the parent.

ON UPDATE CASCADE

More complex, because the pre-update child row doesn't match any parent row with the new key, but does match with the old key. Vitess must update the child before the parent — which requires turning off FOREIGN_KEY_CHECKS for the statement:

SET FOREIGN_KEY_CHECKS=0;
UPDATE child SET parent_id=8 WHERE parent_id=7;
UPDATE parent SET id=8 WHERE id=7;
SET FOREIGN_KEY_CHECKS=1;

Doing this disables all MySQL FK checks for the update, including RESTRICT on any grandchild. Vitess therefore must re-validate those grandchild RESTRICTs in application code before issuing the update. That validation is itself a subtle surface — see the fuzzer bug #1 in the source post (sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints) where a no-op update was incorrectly rejected because the validation JOIN didn't exclude unchanged rows.

ON DELETE SET NULL / ON UPDATE SET NULL

Similar to CASCADE: child must be updated first (set the FK column to NULL), then the parent operation proceeds. Binlog captures both explicitly.

Scope: single-shard only (as of 2023-12-05)

All of the above applies only to unsharded / single-shard databases. Multi-shard FKs (shard-scoped, or cross-shard) are roadmap. From the post: "foreign key constraint support is limited to unsharded/single shard databases. We expect to support shard-scoped foreign key constraints in a multi-shard environment, or even cross-shard foreign keys, but we limit the current discussion to a single shard." The Vitess-owns-FK architectural choice is partially motivated by the cross-shard future, but the implementation lags the architecture.

Cost of Vitess-level FK enforcement

  • More locking than native MySQL — SELECT ... FOR UPDATE on the parent before issuing child DMLs.
  • More round-trips between VTGate and the backing MySQL — at least one extra SELECT + one extra child-side DML per FK depth level.
  • Non-zero performance impact framed in the post. No specific numbers disclosed.

Vitess's normal design philosophy for FK-free workloads is to delegate queries directly to the backing MySQL for performance; for FK workloads, that optimisation is consciously traded off for correctness + CDC-visibility.

Gotchas surfaced by the fuzzer

Four bugs identified by the random-DML fuzzer and fixed in patches, illustrating the subtlety of FK semantics above MySQL:

  1. No-op update + validation JOIN — UPDATE parent SET col=2 WHERE col=2 (row doesn't actually change) was incorrectly rejected by Vitess's validation JOIN under CASCADE+RESTRICT chaining. Fix: exclude unchanged rows from the validation predicate (AND child.col NOT IN (2)).
  2. Arithmetic on VARCHAR returning -0 FLOAT — Vitess's cascade-SELECT returned -0 for col * (col - (col)) on a varchar column; MySQL normalised -0 == 0 when applied back to the child. Fix: CAST the expression to the target column's type (CAST(... AS CHAR)).
  3. Missing gap locks on REPLACE INTO + unique index — cascade SELECT ... FOR UPDATE wasn't gap-locking on a unique key, allowing a concurrent insert to slot in between select and cascade. Fix: promote to NOWAIT to fail-fast instead of block — see patterns/nowait-lock-for-cascade-select.
  4. Cyclic FKs across different tables — prohibited altogether (see concepts/cyclic-foreign-key-prohibition); self-reference remains allowed.

Seen in

Last updated · 550 distilled / 1,221 read