Insertion of data violates the foreign key constraint

Question

Consider I have two tables Employee and Department with the fields given below:

Employee:

Fname, Minit, Lname, Ssn Bdate, Address, Sex, Salary, Super_ssn, Dno

Department:

Dname, Dnumber, Mgr_ssn, Mgr_start_date

Italics and code block represent the primary key of table.

In above case, Super_Ssn references the SSN of employee, while Dno references the Department's Dnumber. Creating the sql for them is pretty straightforward by adding columns first and altering them later when both tables are formed. However, while inserting the data like given below:

insert into employee values('James','E','Borg','888665555','1937-11-10','asda','M',55000,NULL,1);
ERROR:  insert or update on table "employee" violates foreign key constraint "dno_ref_dnum_dpt"
DETAIL:  Key (dno)=(1) is not present in table "department".

and

insert into sp0090.department values ('Headquarter',1,'888665555','1981-06-19');
ERROR:  insert or update on table "department" violates foreign key constraint "department_mgr_ssn_fkey"
DETAIL:  Key (mgr_ssn)=(888665555) is not present in table "employee".

I am getting the foreign key constraint violated. This can be traced because to insert Mgr_ssn in department, it has to exists in Employee and to insert dno in Employee it has to exists in Department. However, I am required to fill the tables with maintaining the schema as it is. How do I solve this circular referencing? Thanks.

Answer 1

In Postgres, there are two ways to circumvent the chicken-and-egg problem of inserting into tables with circular references.

a) Defer one of the constraints, either declaring it as DEFERRABLE INITIALLY DEFERRED when created or as DEFERRABLE as created and then defer it when needed.

For an example of using deferrable constraints, see my answer in this question:
Implementation of a many-to-many relationship with total participation constraints in SQL.

b) Use a single statement to insert into the two (or more) tables, using modifying CTEs. Example for your case:

with
  insert_emp as
  ( insert into employee 
        (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
    values
        ('James', 'E', 'Borg', '888665555', '1937-11-10', 'asda', 'M', 55000, NULL, 1)
    returning 
        *
  ), 
  insert_dep
  ( insert into sp0090.department 
        (Dname, Dnumber, Mgr_ssn, Mgr_start_date) 
    values 
        ('Headquarter', 1, '888665555', '1981-06-19')
    returning 
        *
  ) 
select *
from insert_emp as e
  full join insert_dep as d
  on  e.dno = d.dnumber
  and e.ssn = d.mgr_ssn ;

Answer 2

There is an old article that explains this situation, this is called a circular reference. When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint? I would suggest reading this article below and seeing if it helps.

SQL By Design: The Circular Reference