Right, so you’ve decided to build a Rube Goldberg machine of a database. Congratulations. Circular foreign keys are one of those ideas that seem brilliant in a whiteboard session after three espressos and then become a waking nightmare when you actually have to put data in the thing. I’m talking about a situation where Table A points to Table B, and Table B, in a stunning act of poor judgment, points right back to Table A. Or maybe it’s a longer, more convoluted chain that ultimately loops back on itself. It’s the database equivalent of that “you hang up first / no, you hang up first” conversation we all had in middle school.

The classic, slightly absurd example is an employee table where every employee has a manager, and every manager is, of course, also an employee. So you might have a reports_to foreign key pointing to the employee_id in the same table. But let’s make it truly circular. Imagine we also want to enforce that every employee must have a manager—even the CEO. And to make it truly ridiculous, let’s say we also want to track the “lead technician” for each manager, which points back to a different employee. You can see how this gets messy fast.

The Chicken-and-Egg Problem of INSERTs

Here’s your first brick wall. Let’s build the simple, self-referential table.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    reports_to INT NOT NULL, -- This MUST have a value
    CONSTRAINT fk_employee_manager
        FOREIGN KEY (reports_to)
        REFERENCES employees(employee_id)
);

Now, try to insert the first employee. Go on, I’ll wait.

INSERT INTO employees (employee_id, name, reports_to)
VALUES (1, 'The CEO', 1); -- This will fail.

It fails spectacularly. Why? Because you’re trying to insert a row that references a primary key (reports_to = 1) that, according to the current state of the table, does not exist yet. The foreign key constraint checks the moment the statement runs, and it finds a violation. You cannot satisfy the constraint because the constraint is, by definition, unsatisfiable until the data already exists. It’s the database version of a catch-22.

DEFERRING to the Rescue (Because the Designers Aren’t Sadists)

The SQL standard designers aren’t completely insane. They knew we’d do stupid things like this, so they built an escape hatch: DEFERRABLE constraints.

The concept is simple: instead of checking the constraint immediately after each statement, you tell the database, “Hey, chill. Just make sure everything is kosher by the time I’m done with this whole transaction.” This allows you to insert your CEO with a temporary NULL value or, more elegantly, insert the row and then update the foreign key to point to itself—all within a single transaction before the final check happens.

Here’s how you do it. You have to define the constraint as DEFERRABLE initially DEFERRED or IMMEDIATE. DEFERRED is what you want.

DROP TABLE employees;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    reports_to INT NULL, -- Allow NULL temporarily
    CONSTRAINT fk_employee_manager
        FOREIGN KEY (reports_to)
        REFERENCES employees(employee_id)
        DEFERRABLE INITIALLY DEFERRED -- This is the magic
);

Now, within a transaction, you can do this:

BEGIN;

-- Insert the CEO without a manager (NULL) first.
INSERT INTO employees (employee_id, name, reports_to)
VALUES (1, 'The CEO', NULL);

-- Now update the CEO's manager to be themselves.
UPDATE employees SET reports_to = 1 WHERE employee_id = 1;

-- The constraint is checked HERE, at COMMIT, and it passes.
COMMIT;

The COMMIT is where the database finally runs the check. It looks at the employees table, sees that every reports_to value (which is just 1) exists in the employee_id column, and gives you the green light. You’ve successfully created a closed loop.

The Real-World Advice: Don’t Do This

I just showed you how to do it. Now I’m going to tell you to almost never do it. While DEFERRED constraints solve the technical insertion problem, they often paper over a conceptual design flaw.

Mandatory circular dependencies create a world of pain. What if you need to delete the CEO? The standard ON DELETE actions become nearly useless. CASCADE would wipe out the entire table. RESTRICT would prevent the deletion forever. SET NULL would break the mandatory constraint for everyone who reported to them. It’s a mess.

The pragmatic solution is to break the circle intentionally. Allow NULL for the ultimate top-level record (like the CEO’s reports_to field). This acknowledges the hierarchical reality—someone has to be at the top—and it saves you from a world of transactional gymnastics and operational headaches. You enforce the circular relationship not with a blunt NOT NULL constraint, but through application logic or careful business process. Your database will be simpler, your queries more straightforward, and your sanity intact. Use the circle sparingly, and only when you have a truly, truly good reason.