Circular Foreign Keys: Inserting Rows That Point at Each Other

Circular foreign keys need a legal order of inserts, and NULL is what makes that order possible. Create the department with no manager, create the employee, then fill in the manager. Do all three in one transaction.

The chicken and egg problem

Picture a junior developer asking, “Which table do I insert into first?” A department has a manager, and the manager is an employee. But every employee belongs to a department. Each row needs the other to exist first.

That is a circular foreign key. It is legal, and it is more common than you would think. Let me build the two tables and show you.

IF OBJECT_ID(N'dbo.Department') IS NOT NULL
    ALTER TABLE dbo.Department DROP CONSTRAINT IF EXISTS FK_Department_Manager;
DROP TABLE IF EXISTS dbo.Employee;
DROP TABLE IF EXISTS dbo.Department;
GO
CREATE TABLE dbo.Department
(
    DepartmentId int PRIMARY KEY,
    ManagerId    int NULL
);

CREATE TABLE dbo.Employee
(
    EmployeeId   int PRIMARY KEY,
    DepartmentId int NOT NULL REFERENCES dbo.Department (DepartmentId)
);

ALTER TABLE dbo.Department ADD CONSTRAINT FK_Department_Manager
    FOREIGN KEY (ManagerId) REFERENCES dbo.Employee (EmployeeId);

Both obvious orders fail

Try the department first, with a manager who does not exist yet. Then try the employee first, for a department that does not exist yet. I catch each error so you can see its number.

BEGIN TRY
    INSERT dbo.Department VALUES (1, 10);
END TRY
BEGIN CATCH
    SELECT N'Department first' AS attempt, ERROR_NUMBER() AS error_number;
END CATCH;

BEGIN TRY
    INSERT dbo.Employee VALUES (10, 1);
END TRY
BEGIN CATCH
    SELECT N'Employee first' AS attempt, ERROR_NUMBER() AS error_number;
END CATCH;

Both attempts fail with error 547, the foreign key violation. Neither table accepts a row yet. The cycle has no door.

Use NULL as the way in

ManagerId allows NULL, and that is the door. Insert the department without a manager. Insert the employee. Then update the department. The transaction keeps the three steps together, so nobody sees a department without a manager.

BEGIN TRANSACTION;

INSERT dbo.Department VALUES (1, NULL);
INSERT dbo.Employee VALUES (10, 1);
UPDATE dbo.Department SET ManagerId = 10 WHERE DepartmentId = 1;

COMMIT;

SELECT d.DepartmentId, d.ManagerId, e.DepartmentId AS employee_department
FROM dbo.Department AS d
JOIN dbo.Employee AS e ON e.EmployeeId = d.ManagerId
ORDER BY d.DepartmentId;

One row comes back: department 1, manager 10, and the employee’s department is 1.

A transaction does not delay the check

Here is a common mistake. People think a transaction postpones foreign key checks until COMMIT. It does not. SQL Server checks each statement as it runs. Watch an employee for a missing department 2 fail on the spot.

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
    INSERT dbo.Employee VALUES (20, 2);
    INSERT dbo.Department VALUES (2, 20);
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

SELECT COUNT(*) AS employee_count FROM dbo.Employee;

The error is 547 again, and the message names the Employee table, so the first INSERT is what failed. The rollback removes everything, and the table still holds just employee 10. The valid rows from before are untouched.

What the cycle does not promise

The foreign keys prove that rows exist. They do not prove the manager works in the same department. Add employee 30 to a new department 2, then make that employee the manager of department 1.

INSERT dbo.Department VALUES (2, NULL);
INSERT dbo.Employee VALUES (30, 2);
UPDATE dbo.Department SET ManagerId = 30 WHERE DepartmentId = 1;

SELECT d.DepartmentId, d.ManagerId, e.DepartmentId AS employee_department
FROM dbo.Department AS d
JOIN dbo.Employee AS e ON e.EmployeeId = d.ManagerId
ORDER BY d.DepartmentId;

It works. Department 1 is now managed by an employee from department 2. If that is wrong for your business, enforce it in a reviewed write path or a composite key. The cycle will not do it for you.

Find cycles on your own server

Do you have circles hiding in your database? This query lists pairs of tables that reference each other. It only finds two-table cycles, but those are the usual ones. Run it in your own database; here it should return our Department and Employee pair.

SELECT OBJECT_NAME(a.parent_object_id) AS table_a,
       OBJECT_NAME(a.referenced_object_id) AS table_b
FROM sys.foreign_keys AS a
JOIN sys.foreign_keys AS b
  ON b.parent_object_id = a.referenced_object_id
 AND b.referenced_object_id = a.parent_object_id
WHERE a.parent_object_id < a.referenced_object_id
ORDER BY table_a, table_b;

If the manager is optional anyway, there is a cleaner design. Put the assignment in its own table, with one row per department and the employee who manages it. Then the cycle disappears, and so does the special insert order.

Clean up in the right order

You cannot just drop the tables, since each one is referenced by the other. Drop the manager foreign key first, then the tables.

ALTER TABLE dbo.Department DROP CONSTRAINT FK_Department_Manager;
DROP TABLE dbo.Employee;
DROP TABLE dbo.Department;

Next time two tables point at each other, find the nullable column and start there.

A circular key is not a deferred check, it is a dependency that needs a legal first step.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.