A foreign-key cycle where every column is NOT NULL cannot take its first row

Make `departments.manager_id` NOT NULL referencing `employees`, and `employees.department_id` NOT NULL referencing `departments`, and you have a schema that is valid, a diagram that looks tidy, a migration that applies cleanly, and a database that will never accept a single row ^[1]. Every insert requires a row that does not exist yet ^[2], and the bill does not arrive at review: it arrives the first time somebody points a seed script at an empty database, which means a new staging environment, a contributor's laptop, a fresh tenant, or the disaster-recovery rehearsal ^[3].

Production is not affected, which is exactly why the defect survives. It was populated years ago by whoever fought through it once, and only the empty case exposes the shape ^[4]. The reason nobody catches it earlier is structural rather than careless: the cycle is a property of the whole graph, not of any one relationship, so there is nothing wrong with either foreign key when you look at it on the diagram ^[5].

Two problems get conflated here and they have different answers. Creating the pair is a DDL ordering question: the first `CREATE TABLE` cannot reference a table that does not exist, so the second constraint goes on afterwards with `ALTER TABLE ... ADD CONSTRAINT`, which is mechanical, works on every engine, and leaves the catalog perfectly content ^[6]. That confusion is old. On 22 June 2000 Eric Du asked the PostgreSQL mailing list why he could not create two mutually referencing tables; his `INITIALLY DEFERRED` attempt failed at `CREATE TABLE` with `ERROR: Relation 't2' does not exist`, because deferral postpones checking data, not the existence of a table that has not been created yet ^[7].

Inserting into the pair turns entirely on nullability. If one column in the cycle is nullable, there is no problem: insert the department with `manager_id` NULL, insert the employee pointing at it, update the department ^[8]. If all of them are NOT NULL, no ordering works, because ordering is not the difficulty; the set of rows the schema demands is self-referential, and no sequence of statements produces a set that contains itself ^[9].

The escape hatch is deferred checking at `COMMIT` with both inserts in one transaction, and whether you have it is decided by the engine, not the model ^[10]. MySQL's manual states plainly that because MySQL does not support deferred constraint checking, `NO ACTION` is treated as `RESTRICT` ^[11]. SQL Server has no deferrable constraints either ^[12]. Even where deferral exists it is narrower than it looks: the PostgreSQL `CREATE TABLE` documentation limits the clause to UNIQUE, PRIMARY KEY, EXCLUDE and REFERENCES, and says NOT NULL and CHECK are not deferrable ^[13]. So the NOT NULL is still checked immediately, and you must insert the department pointing at an employee id that does not exist yet, minting the key yourself from a sequence or as a client-side UUID ^[14].

That leaves three remedies, all modelling decisions: make one side nullable, defer on an engine that can, or move the reference into a third table ^[15]. On MySQL and SQL Server, only two of those three are available ^[18].

Worth watching: whether your pipeline ever runs a seed against a genuinely empty database, because that is the only test that fails. Schemity, a desktop ERD tool whose author wrote the source post, computes the cycle from the open diagram with a rule named `fk-cycle-all-not-null` and marks the entities in the margin ^[16]; the same check is expressible as a graph query over your own catalog. Twenty-six years after Du's question, the modelling version of the knot is still being written up fresh ^[17].