Skip to content

CONCEPT Cited by 1 source

Cyclic foreign key prohibition

Definition

Cyclic foreign key prohibition is the restriction on Vitess's foreign-key constraint support (as of 2023-12-05) that forbids FK cycles across different tables while continuing to allow self-referencing tables (a single table pointing at itself).

Verbatim from the post:

"While self-referencing tables are supported, cyclic foreign key references between different tables is not allowed." (Source: sources/2026-04-21-planetscale-the-challenges-of-supporting-foreign-key-constraints)

Allowed:

-- Self-reference: a table pointing to itself.
CREATE TABLE employees (
  id       INT PRIMARY KEY,
  manager_id INT,
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

Not allowed:

-- Cyclic cross-table: A → B → A
CREATE TABLE a (id INT PRIMARY KEY, b_id INT, FOREIGN KEY (b_id) REFERENCES b(id));
CREATE TABLE b (id INT PRIMARY KEY, a_id INT, FOREIGN KEY (a_id) REFERENCES a(id));
-- Rejected by Vitess.

Why the asymmetry

The source post states the limitation without unpacking the reasoning; the mechanism the post does disclose suggests two plausible contributing factors (speculative, not stated canonically):

  1. Cascade orchestration recursion boundary. Vitess's cascade orchestration is a depth-first walk from parent → child → grandchild. A FK cycle across different tables makes the walk unbounded in general (A.delete → B.delete → A.delete → ...). Self-reference is a special case where the same table appears — cascade termination is easier to analyse within a single table's row set (the rows participating in the cycle are in one place, bounded by the table's own cardinality).
  2. Deploy-path computation. FK-aware schema path computation (see schemadiff) has to find a valid intermediate-state-is-valid ordering for the migration steps. Cycles in the FK dependency graph mean no topological order exists — you can't create all tables FK-valid in any sequence. Forbidding cycles keeps the schema's dependency graph a DAG (see concepts/dag-vs-cyclic-workflow), which schemadiff can topologically sort.

Neither rationale is verbatim in the post; PlanetScale states the property + documents it in the user-facing foreign-key docs.

How MySQL/InnoDB itself handles this

Vanilla MySQL/InnoDB allows cyclic FKs between different tables — you can define them via alternating CREATE TABLE (with FK referenced-but-not-enforced until both exist) + ALTER TABLE ... ADD FOREIGN KEY. Writes that would close a cycle (e.g. two mutually-referential rows that need to both exist before either can) require SET FOREIGN_KEY_CHECKS=0 or careful atomic insertion.

Vitess's prohibition is therefore stricter than MySQL's — part of the broader pattern of Vitess tightening MySQL's FK model (cascades go through the binlog; cycles forbidden; constraint names auto-generated per deployment) in exchange for binlog-CDC-friendly, online-DDL-compatible, and deploy-path-computable semantics.

Implication for schema design

Teams coming from a vanilla-MySQL FK model where cyclic references between different tables are allowed must refactor such schemas before migrating to PlanetScale. The usual refactor: break the cycle with an association table (a third table that references both sides, making the graph a tree with a join root) or with a nullable FK that's populated only after both rows exist.

Seen in

Last updated · 550 distilled / 1,221 read