Circular Foreign Keys: Why the First Row Cannot Be Inserted

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples. TL;DR: When foreign keys form a cycle and every column in it is NOT NULL, each insert needs a row that does not exist yet, so an empty database can never take its first row. The cycle is a property of the whole graph rather than of any one relationship, so nobody spots it by looking at the diagram - it surfaces at seed time on a fresh environment. Schemity's fk-cycle-all-not-null lint rule computes it from the open ERD offline and marks the entities involved in the margin, and the fix is a modelling decision: make one side nullable, defer the check on an engine that can, or move the reference into a third table. A cycle of foreign keys is legal to create and impossible to populate. If departments.manager_id is NOT NULL and references employees , and employees.department_id is NOT NULL and references departments , the schema is valid, the diagram is tidy, the migration applies cleanly, and the database will never accept a single row. Each insert needs a row that does not exist yet. That failure has a specific arrival time, and it is not review. It arrives the first time somebody points the seed script at an empty database: a new staging environment, a contributor's local machine, a fresh tenant, the disaster-recovery rehearsal. Production is fine, because production was populated years ago by whoever fought through it once. The defect sat in the schema the whole time and only the empty case exposes it. It is also old. On 22 June 2000, Eric Du asked the PostgreSQL mailing list why he could not create two tables that were foreign keys for each other - 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. Twenty-six years later the modelling version of the same knot is still being written up: a clear description of it puts the problem in one sentence, that running two independent inserts will not work because you cannot insert into department without a manager_id and cannot insert into employee without a department_id . Can two tables have foreign keys to each other? Yes, and this is worth separating from the insert problem, because the two get conflated constantly. Creating the pair is a DDL ordering question. The first CREATE TABLE cannot reference a table that does not exist, so the second constraint is added afterwards with ALTER TABLE ... ADD CONSTRAINT . That is a mechanical detail, it works on every engine, and once both constraints exist the catalog is perfectly happy. Nothing about the shape is rejected. Inserting into the pair is a different question with a different answer, and the answer depends entirely on one thing: whether every foreign key column in the cycle is NOT NULL . If one of them is nullable, there is no problem at all. Insert the department with manager_id NULL, insert the employee pointing at it, update the department. Two statements and an update, done once, at seed time. If all of them are NOT NULL , there is no ordering that 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. Which databases let you defer the foreign key check? The escape hatch is deferral: tell the engine to check the constraint at COMMIT rather than after each statement, insert both rows inside one transaction, and let the two halves validate each other at the end. Whether you have that hatch is decided by the engine, not by the model. | Engine | Deferrable foreign keys | What a NOT NULL cycle means here | |---|---|---| | PostgreSQL | Yes, DEFERRABLE INITIALLY DEFERRED | Both inserts in one transaction, if you can supply the key values yourself | | Oracle | Yes, for constraints other than NOT NULL | Same | | SQLite | Yes, with PRAGMA foreign_keys = ON and an explicit transaction | Same, and outside an explicit transaction deferred constraints behave as immediate | | MySQL and MariaDB | No | No transactional escape hatch at all | | SQL Server | No | No transactional escape hatch at all | MySQL's own manual is blunt about it, and states in the foreign key constraints page that because MySQL does not support deferred constraint checking, NO ACTION is treated as RESTRICT . SQL Server has no deferrable constraints either. On those engines a cycle of NOT NULL foreign keys is not a puzzle to solve in the transaction, it is a schema you cannot use. And deferral is narrower than it first appears even where it exists. The PostgreSQL CREATE TABLE documentation is explicit that only UNIQUE , PRIMARY KEY , EXCLUDE and REFERENCES constraints accept the clause, and that NOT NULL and CHECK constraints are not deferrable. So the NOT NULL on department.manager_id is still checked immediately. You are not allowed to insert the department with no manager and fix it at commit - you have to insert it pointing at an employee id that does not exist yet, which means generating the key yourself from a sequence or as a client-side UUID before either row is written. Deferral does not remove the chicken and egg. It relocates it into your insert code, where it becomes a requirement that the application knows both keys in advance. The three shapes a foreign key cycle takes Only one of these is visible by eye, which is the whole difficulty. One table. A self-reference: categories.parent_id NOT NULL referencing categories.id . The root category has no parent and cannot be written. This one is obvious in hindsight and still ships regularly, usually because parent_id was made NOT NULL for the honest reason that most rows do have a parent. Two tables. The mutual pair - departments and employees , organizations and owners , carts and checkouts . Visible on a diagram if the two entities happen to sit next to each other, invisible if they are eighty tables apart on a large canvas. Three or more. A ring: A references B , B references C , C references A . Nobody drew this and nobody can see it. Each of the three relationships is individually reasonable, each was added in a different quarter, and the cycle exists only in the graph they form together. No amount of staring at the ERD finds it, because there is no place on the diagram where the defect is located - it is a property of the whole model, in exactly the sense that a foreign key whose type does not match the key it references is a fact about two tables at once rather than about either one. How do you find a cycle you cannot see? You compute it. Schema lint in Schemity ships seventeen rules, and fk-cycle-all-not-null is one of the three in the group that fails at runtime: a cycle of foreign keys in which every column is NOT NULL , with a table referencing itself as the simplest case. It walks the relationships in the open diagram, so the three-table ring is found on exactly the same terms as the self-reference - the number of hops makes no difference to a graph traversal and all the difference to a person. Both invisible shapes are sitting in that one panel. table5 > table6 is the mutual pair and table1 > table3 > table2 is the ring, and the rule reports them identically because to a traversal they are one defect at two lengths. Look at the canvas underneath and the ring is three unremarkable relationships, each drawn between a different pair of tables, none of which says cycle by itself. The finding names the hops in order - table1 → table3 → table2 → table1 - says what it costs, that each INSERT needs a row that does not exist yet, and states both fixes in the same breath: make one of these foreign keys nullable, or declare them DEFERRABLE on PostgreSQL and insert the rows in one transaction. Two properties of how that finding is reported matter more than the check itself. It runs against the model, not the server. Every input the rule needs - which relationships exist, which columns are nullable - is already written down in the diagram, so the check needs the schema rather than a connection, in exactly the way a rule reading which date columns are missing their time zone needs nothing but the types in front of it. You get the answer while designing, on a plane, before the migration exists, rather than at seed time on an environment that does not exist yet. And it lands on the diagram. Every finding carries a Show on canvas link, and taking it draws a colored strip in the margin beside each entity in the cycle, at the exact field row concerned, rather than leaving you with a list to translate back into the picture - the orange strip visible on the entity at the top of that canvas is the same mechanism reporting the unrelated change_histories.action_type finding. Nullability is already legible there: a nullable field carries a green N badge on the entity, so once you break the cycle, the thing that broke it is visible on the canvas at a glance instead of being a fact you have to remember. Clicking the relationship highlights both ends, the foreign key field on one entity and the primary key it points at on the other, which is how you trace a ring back through its hops. If the cycle is deliberate and you have solved it with deferral, ignore that single finding. The ignore is saved in the diagram's JSON file and travels with it, so the decision is recorded once for the team rather than re-dismissed by each person who opens the file. One honest limit: Schemity has no DEFERRABLE toggle on a relationship. It models cardinality, ON DELETE and ON UPDATE , and its answer to a cycle is the modelling decision rather than a constraint flag - so a deferrable constraint is something you add in the migration and record in the ignore, not something the ERD holds for you. Which side should be nullable? Assuming you are not deferring, one column in the cycle has to accept NULL, and the choice is not arbitrary. Pick the side where absence is a real stat