Consider I have two tables
Department with the fields given below:
Fname, Minit, Lname,
Ssn Bdate, Address, Sex, Salary, Super_ssn, Dno
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.