Search code examples
databasepostgresqldatabase-designforeign-keysconstraints

Chicken-and-egg problem in with foreign keys

How do I create and insert rows to the following table schema in PostgreSQL:

Table: employee

emp_id, emp_name, emp_dept, emp_manager

Table: department

dept_id, dept_name, dept_manager

emp_manager is a foreign key to employee(emp_id)

emp_dept is a foreign key to department(dept_id)

dept_manager is a foreign key to employee(emp_id)

Solution

  • It can work like this:

    CREATE TABLE employee (
  emp_id int PRIMARY KEY
, emp_dept int NOT NULL
, emp_manager int
, emp_name text NOT NULL
, CONSTRAINT fk_emp_manager FOREIGN KEY (emp_manager) REFERENCES employee(emp_id)
, UNIQUE (emp_dept, emp_id)  -- needed for FK fk_dept_manager
);

CREATE TABLE department (
  dept_id int PRIMARY KEY
, dept_manager int
, dept_name text NOT NULL
, CONSTRAINT fk_dept_manager FOREIGN KEY (dept_id, dept_manager) REFERENCES employee(emp_dept, emp_id)
);

ALTER TABLE employee
  ADD CONSTRAINT fk_emp_dept
  FOREIGN KEY (emp_dept) REFERENCES department(dept_id);

    Note how I change fk_dept_manager into a multicolumn FK reference to only allow employees of the same department to be department manager. Assuming you want that.

    You might also want a CHECK constraint in table employee to disallow employees from being their own manager:

    CHECK (emp_manager <> emp_id)

    How to INSERT?

    As usual. To overcome mutual dependencies, either make FK constraints DEFERRABLE and run multiple commands in a single transaction (more expensive) or use a single command with one or more CTEs.

    Example: to insert a new department and a new employee as its manager at once:

    WITH ins_dept AS (
   INSERT INTO department
          (dept_manager                                         , dept_name)          
   VALUES (nextval(pg_get_serial_sequence('employee', 'emp_id')), 'Sales')
   RETURNING *
   )
INSERT INTO employee
      (emp_id      , emp_dept, emp_manager, emp_name)
SELECT dept_manager, dept_id , NULL, 'Bob'
FROM   ins_dept;

    db<>fiddle here

    Further reading: