Foreign keys are the backbone of referential integrity in relational databases, and PostgreSQL is no exception. Any application that depends on correct data will lean on them heavily. There is, however, a configuration that trips people up: circular dependencies.

The department-and-employee deadlock

Suppose the schema stores departments and employees. Each department requires a leader, and each employee must belong to a department. A department cannot exist without its leader; an employee cannot exist without a department. Modelled directly, that produces a cycle between the two tables.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE TABLE department (

   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

   name text NOT NULL UNIQUE,

   leader bigint NOT NULL

);

CREATE TABLE employee (

   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

   name text NOT NULL,

   department bigint REFERENCES department NOT NULL

);

ALTER TABLE department

   ADD FOREIGN KEY (leader) REFERENCES employee;

With the constraints in place as written, neither table accepts inserts. Attempting to populate one side violates the foreign key pointing at the other:

1

2

3

4

INSERT INTO department (name, leader) VALUES ('hans', 1);

ERROR:  insert or update on table "department" violates

     foreign key constraint "department_leader_fkey"

DETAIL:  Key (leader)=(1) is not present in table "employee".

Starting from the other table gives the same result. Both paths produce a comparable error, leaving two tables that cannot be filled at all.

Deferring the check until COMMIT

INITIALLY DEFERRED resolves the cycle. Applied to the constraint, it instructs PostgreSQL not to validate the foreign key at the moment of the write, but only at COMMIT. Inside the transaction, operations may then run in any order as long as every constraint holds by the time the transaction commits.

1

2

3

ALTER TABLE department

     ADD FOREIGN KEY (leader)

     REFERENCES employee DEFERRABLE INITIALLY DEFERRED;

Defining the foreign key on department this way makes an ordinary single-transaction insert work without violating anything:

1

2

3

4

5

6

7

BEGIN;

INSERT INTO department (name, leader)

      VALUES ('hq', 0) RETURNING id;

INSERT INTO employee (name, department)

      VALUES ('hans', 1);

UPDATE department SET leader = 1 WHERE id = 1;

COMMIT;

The transaction completes normally, which means complex dependencies can be handled without tracking insertion order by hand.

For delete performance on tables with foreign keys, indexing those keys is worth reviewing.