Deferred Foreign Key Constraints

-- Create tables with deferrable constraints
CREATE TABLE departments (
  id         SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id INT
);

CREATE TABLE employees (
  id            SERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  department_id INT NOT NULL
    REFERENCES departments(id)
    DEFERRABLE INITIALLY DEFERRED
);

ALTER TABLE departments
  ADD CONSTRAINT fk_manager
  FOREIGN KEY (manager_id) REFERENCES employees(id)
  DEFERRABLE INITIALLY DEFERRED;

-- Now we can insert both in one transaction
BEGIN;
  INSERT INTO departments (id, name, manager_id) VALUES (1, 'Engineering', 1);
  INSERT INTO employees (id, name, department_id) VALUES (1, 'Alice', 1);
COMMIT;  -- Constraints checked here