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


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


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".


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.


2 Answers 2


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:

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

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

  • Nice and the article is relevant. But it is mainly about SQL Server. Postgres has implemented DEFERRED constraints that can solve the issue of inserting and updating into tables with circular references. Commented Nov 13, 2016 at 16:01
  • Awesome, thank you for the heads up I wasn't aware of this. Commented Nov 13, 2016 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.