Two tables reference each other with foreign keys — an employee row must point at a department, and each department row must point at its manager employee. Neither table can be populated first. How do you insert the very first pair of rows, and what are the portable alternatives?

middleshould knowfreq 38%

answer

Cycle = no valid statement order
DEFERRABLE INITIALLY DEFERRED both rows one tx
Portable fallback: nullable FK + follow-up UPDATE
FK ignores NULLs
Never disable FK checks to work around it

basics

~20 s

Declare at least one of the two foreign keys DEFERRABLE INITIALLY DEFERRED, then insert both rows in one transaction; the checks run at COMMIT when both parents exist. The portable alternative is a nullable foreign key: insert with NULL, then UPDATE it.

solid answer

~50 s

There is no valid statement order, because each row's parent is the other row. Two designs solve it:

Deferred checking. Make one or both foreign keys DEFERRABLE INITIALLY DEFERRED (or DEFERRABLE INITIALLY IMMEDIATE plus SET CONSTRAINTS ... DEFERRED in the transaction). Insert the department and the employee in the same transaction in any order; at COMMIT both referenced rows exist, so both checks pass. This keeps both columns NOT NULL, which is the stronger model.
Nullable link, filled in afterwards. Make one side — usually department.manager_id — nullable. Insert the department with NULL, insert the employee referencing it, then UPDATE the department to set the manager. This works on every engine, including MySQL and SQL Server which have no deferral, at the cost of a column that can legally be NULL.

Avoid the third "solution" you sometimes hear: dropping the constraint or disabling foreign-key checking for the load. That is a global weakening, it lets genuinely bad data commit, and re-enabling requires a full validation scan.

There is no valid statement order, because each row's parent is the other row. Two designs solve it: 1. **Deferred checking.** Make one or both foreign keys `DEFERRABLE INITIALLY DEFERRED` (or `DEFERRABLE INITIALLY IMMEDIATE` plus `SET CONSTRAINTS ... DEFERRED` in the transaction). Insert the department and the employee in the same transaction in any order; at COMMIT both referenced rows exist, so both checks pass. This keeps both columns NOT NULL, which is the stronger model. 2. **Nullable link, filled in afterwards.** Make one side — usually `department.manager_id` — nullable. Insert the department with NULL, insert the employee referencing it, then UPDATE the department to set the manager. This works on every engine, including MySQL and SQL Server which have no deferral, at the cost of a column that can legally be NULL. Avoid the third "solution" you sometimes hear: dropping the constraint or disabling foreign-key checking for the load. That is a global weakening, it lets genuinely bad data commit, and re-enabling requires a full validation scan.

code

sql · 9 lines

sql

ALTER TABLE department
  ADD CONSTRAINT department_manager_fk FOREIGN KEY (manager_id)
  REFERENCES employee (id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
INSERT INTO department (id, name, manager_id) VALUES (1, 'Platform', 100);
INSERT INTO employee   (id, name, dept_id)    VALUES (100, 'Ada', 1);
COMMIT;  -- both FK checks run here and pass

go deeper

for a junior

Recognise the chicken-and-egg problem and give the nullable-column-then-UPDATE fix inside one transaction.

for a middle

Give both solutions, know that only one of the two keys needs to be deferrable, and know that foreign keys ignore NULLs.

for a senior

Argue the tradeoff — NOT NULL plus deferral versus nullable plus immediate checks — and rule out disabling enforcement, explaining that re-enabling costs a full validation scan.

for a principal

Question the model itself: many FK cycles indicate a mandatory reference that should be optional, or a relationship that belongs in its own assignment table with a lifecycle instead of a column.

Why there is no insertion order

A foreign key says: the value in this column must match an existing row in the referenced table (or be NULL). When two tables reference each other and both columns are NOT NULL, the very first pair of rows forms a cycle: inserting the employee needs the department to exist, inserting the department needs the employee to exist. Immediate, per-statement checking rejects whichever you attempt first. This is not a bug — it is exactly the guarantee immediate checking gives you, applied to a model that is only consistent as a pair.

Note the shape of the problem: it appears whenever the reference graph has a cycle, not only in the classic employee/department example. Self-referencing hierarchies with a NOT NULL parent, double-entry ledgers where each entry names its counter-entry, and orders that must name their "current" revision while the revision names the order all show it.

Solution 1 — deferred checking

Declare one or both foreign keys deferrable and let the check run at COMMIT:

Both rows are inserted inside one transaction, in any order.
The engine queues the pending foreign-key checks instead of evaluating them at statement end.
At COMMIT both referenced rows exist, so both checks pass and the transaction lands atomically.

The payoff is that both columns stay NOT NULL — the schema says what you actually mean ("a department always has a manager"), and no application code has to cope with a NULL that only exists because of an insertion-order accident. Only one of the two keys strictly needs to be deferrable; deferring the one on the "less natural" direction (department → manager) is usually enough and keeps immediate diagnostics on the busier path.

The cost is the general cost of deferral: a violation surfaces at COMMIT with a constraint name but no statement attribution, and the whole transaction aborts.

Solution 2 — nullable link and a follow-up UPDATE

Make department.manager_id nullable:

INSERT the department with manager_id = NULL.
INSERT the employee pointing at the department.
UPDATE the department to set manager_id.

All three run in one transaction, so no other session ever sees the half-built pair. Foreign keys ignore NULL values, so step 1 passes immediate checking.

This is the portable answer. MySQL/InnoDB and SQL Server have no deferrable constraints at all, so on those engines it is not merely an alternative — it is the only clean option. Many teams choose it even on PostgreSQL and Oracle because it keeps every check immediate and every error attributable.

Its cost is honest but real: the column is nullable in the schema, so nothing at the database level stops a department from staying managerless forever. If that matters, you have to police it elsewhere — a periodic audit query, or an application invariant.

The anti-pattern: disabling enforcement

A third suggestion appears constantly and should be argued down: drop the foreign key, or switch off foreign-key checking for the session, do the inserts, then put it back. Three problems:

Disabling is not deferring. During the window, genuinely invalid rows can be inserted and committed, and nothing revalidates them afterwards on some engines.
Restoring the constraint means a full validation scan of the table under a strong lock — trivial on an empty table, a production incident on a large one.
It is a table-wide or session-wide weakening applied to solve a two-row problem.

Choosing between the two real options

Ask whether the reference is genuinely mandatory. If a department truly cannot exist without a manager, the model wants NOT NULL on both sides and deferral is the honest mechanism. If "managerless department" is a legitimate transient state in the business (a manager resigned; the seat is open), then the column should be nullable and you never needed deferral at all — the cycle was an artefact of over-constraining the model.

That question is worth asking out loud in an interview, because it often dissolves the problem: many apparent FK cycles are really a modelling smell where one direction should be optional, or where the "current manager" belongs in a separate assignment table with its own lifecycle rather than as a column on the department.

## Why there is no insertion order A foreign key says: the value in this column must match an existing row in the referenced table (or be NULL). When two tables reference each other and both columns are NOT NULL, the very first pair of rows forms a cycle: inserting the employee needs the department to exist, inserting the department needs the employee to exist. Immediate, per-statement checking rejects whichever you attempt first. This is not a bug — it is exactly the guarantee immediate checking gives you, applied to a model that is only consistent as a *pair*. Note the shape of the problem: it appears whenever the reference graph has a cycle, not only in the classic employee/department example. Self-referencing hierarchies with a NOT NULL parent, double-entry ledgers where each entry names its counter-entry, and orders that must name their "current" revision while the revision names the order all show it. ## Solution 1 — deferred checking Declare one or both foreign keys deferrable and let the check run at COMMIT: - Both rows are inserted inside one transaction, in any order. - The engine queues the pending foreign-key checks instead of evaluating them at statement end. - At COMMIT both referenced rows exist, so both checks pass and the transaction lands atomically. The payoff is that both columns stay NOT NULL — the schema says what you actually mean ("a department always has a manager"), and no application code has to cope with a NULL that only exists because of an insertion-order accident. Only one of the two keys strictly needs to be deferrable; deferring the one on the "less natural" direction (department → manager) is usually enough and keeps immediate diagnostics on the busier path. The cost is the general cost of deferral: a violation surfaces at COMMIT with a constraint name but no statement attribution, and the whole transaction aborts. ## Solution 2 — nullable link and a follow-up UPDATE Make `department.manager_id` nullable: 1. INSERT the department with `manager_id = NULL`. 2. INSERT the employee pointing at the department. 3. UPDATE the department to set `manager_id`. All three run in one transaction, so no other session ever sees the half-built pair. Foreign keys ignore NULL values, so step 1 passes immediate checking. This is the portable answer. MySQL/InnoDB and SQL Server have no deferrable constraints at all, so on those engines it is not merely an alternative — it is the only clean option. Many teams choose it even on PostgreSQL and Oracle because it keeps every check immediate and every error attributable. Its cost is honest but real: the column is nullable in the schema, so nothing at the database level stops a department from *staying* managerless forever. If that matters, you have to police it elsewhere — a periodic audit query, or an application invariant. ## The anti-pattern: disabling enforcement A third suggestion appears constantly and should be argued down: drop the foreign key, or switch off foreign-key checking for the session, do the inserts, then put it back. Three problems: - Disabling is not deferring. During the window, genuinely invalid rows can be inserted *and committed*, and nothing revalidates them afterwards on some engines. - Restoring the constraint means a full validation scan of the table under a strong lock — trivial on an empty table, a production incident on a large one. - It is a table-wide or session-wide weakening applied to solve a two-row problem. ## Choosing between the two real options Ask whether the reference is genuinely mandatory. If a department truly cannot exist without a manager, the model wants NOT NULL on both sides and deferral is the honest mechanism. If "managerless department" is a legitimate transient state in the business (a manager resigned; the seat is open), then the column *should* be nullable and you never needed deferral at all — the cycle was an artefact of over-constraining the model. That question is worth asking out loud in an interview, because it often dissolves the problem: many apparent FK cycles are really a modelling smell where one direction should be optional, or where the "current manager" belongs in a separate assignment table with its own lifecycle rather than as a column on the department.

Do both foreign keys have to be deferrable, or just one?
Just one. Deferring either direction breaks the cycle: you insert the row whose check is deferred first, then the other row satisfies its immediate check normally, and the deferred check passes at COMMIT. Keeping the other side immediate preserves fast, attributable errors on the more frequently written path.
How would you solve this on MySQL, which has no deferrable constraints?
Use the nullable-link design: insert the parent with a NULL reference, insert the child, then UPDATE the parent — all in one transaction. The blunt alternative, turning off foreign-key checking for the session, disables enforcement rather than postponing it, so invalid rows can be committed and are never revalidated; treat it as a bulk-import tool, not an application pattern.

saying these in an interview costs you the question

Proposing to drop or disable the foreign key for the insert instead of deferring it
Thinking deferral is needed when one side could simply be nullable — an over-constrained model
Claiming a foreign key rejects NULL values
Believing the two inserts must be in separate transactions
Assuming every engine supports DEFERRABLE

Two tables reference each other with foreign keys — an employee row must point at a department, and each department row must point at its manager employee. Neither table can be populated first. How do you insert the very first pair of rows, and what are the portable alternatives?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why there is no insertion order

Solution 1 — deferred checking

Solution 2 — nullable link and a follow-up UPDATE

The anti-pattern: disabling enforcement

Choosing between the two real options

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions7 questions

▾related topics3 topics

Why there is no insertion order

Solution 1 — deferred checking

Solution 2 — nullable link and a follow-up UPDATE

The anti-pattern: disabling enforcement

Choosing between the two real options

answer

go deeper

context