Sometimes cyclic foreign keys can’t be avoided, but modern SQL features make it quite easy to insert new data without a big hussle.
Let’s assume we have the following two tables:
create table department ( id integer primary key, name varchar(50) not null unique manager_id integer not null ); create table employee ( id integer primary key, name varchar(50) not null, department_id integer not null ); alter table department add foreign key (manager_id) references employee; alter table employee add foreign key (department_id) references department;
As both foreign key columns are defined as
NOT NULL, the usual approach of first inserting a
NULL value first and update the row later with an existing value is not applicable.
Note that in reality, I would expect at least one of those columns to be nullable though.
Running two independent inserts won’t work as we can’t insert into the
department table without a
manager_id and we can’t insert into the
employee table without a
With modern SQL, we do however have two approaches to insert departments and employees that reference each other.
The problem stems from the fact that the database evaluates the constraints at the end of the
INSERT statement. But when the first
INSERT is executed, the referenced values for the second table don’t exists (and vice versa).
A solution to this problem is a constraint that is checked when the transaction is committed, rather then after each
Precisely for this situation, Postgres (and Oracle) allow constraints to be defined as “deferrable” which enables just that: an evaluation of the constraint at
The constraint can be defined as “always deferred” or you can turn the “deferrable” behaviour on or off when you need it:
alter table department add foreign key (manager_id) references employee deferrable initially immediate; alter table employee add foreign key (department_id) references department deferrable initially immediate;
After creating the constraints like that, they behave exactly like any other constraint: they are evaluated when running the DML statement (because of the
INITIALLY IMMEDIATE option).
deferrable attribute allows us to defer the check until the end of the transaction. This is done using the
SET CONSTRAINTS command:
With that command it’s either possible to do this for a named constraint instead or for all constraints that allow to be deferred.
So with the following script, we can insert the new department and all employees without an error:
begin transaction; set constraints all deferred; insert into department (id, name, manager_id) values (1, 'Maintenance', 42); insert into employee (id, name, department_id) values (1, 'Arthur', 1), (2, 'Ford', 1), (42, 'Marvin', 1); commit;
employee_id 42 doesn’t exist when the department is inserted. But the database does not complain, because the foreign key will only be checked when the
COMMIT is issued. After inserting the employees all foreign keys are correct and the
COMMIT will succeed.
Setting a constraint to deferrable (either as
INITIALLY DEFERRED or through a
SET CONSTRAINTS statement), incurs a memory overhead that might be substantial when processing many rows. If inserting depending rows is the exception rather then the common case, it is recommended to define the constraints as
INITIALLY IMMEDIATE and only make them deferred when really needed.
Data modifying common table expressions.
Postgres offers an even more elegant solution: it’s possible to combine the two
INSERT statements into a single statement using a common table expression.
Because it’s a single statement, there is no need to define the constraints as deferrable, as the constraints are checked at the end of the statement.
The above inserts can be rewritten to the following statement (without the need for deferrable constraints)
with new_deparment as ( insert into department (id, name, manager_id) values (1, 'Maintenance', 42); ) insert into employee (id, name, department_id) values (1, 'Arthur', 1), (2, 'Ford', 1), (42, 'Marvin', 1);
The advantage of this is that it’s also possible even when it’s not possible to use explicit transactions (e.g. because the connection is set to auto-commit).
While it’s best to avoid cyclic foreign keys to begin with, it is rather easy to deal with them when needed with modern SQL.