Circular referencing required for class? I'm taking a class called Intro To Database Systems.  I'm given a diagram and I need to create tables for it in SQL server.  However, the diagram appears to have circular referencing.  Am I correct in thinking that?  If so, how do I implement it?  Here is the diagram.



As you can see, DNO in EMPLOYEE references DNUMBER in DEPARTMENT, and MGRSSN in DEPARTMENT references SSN in EMPLOYEE.

Here is my code so far, which doesn't work for obvious reasons.

CREATE TABLE EMPLOYEE (
FNAME   Char(20)    NOT NULL,
MiNIT   Char(1)     NULL,
LNAME   Char(20)    NOT NULL,
SSN     Integer     NOT NULL,
BDATE   Date        NOT NULL,
E_ADDRESS   Char(35)NOT NULL,
SEX     Char(1)     NOT NULL,
SALARY  Money       NOT NULL,
SUPERSSN    Integer NULL,
DNO     Integer     NOT NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (SSN),
CONSTRAINT DNO_Relationship FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER),
CONSTRAINT SUPERSSN_Relationship FOREIGN KEY (SUPERSSN)
REFERENCES EMPLOYEE(SSN)
);

CREATE TABLE DEPARTMENT (
DNAME   Char(20)    NOT NULL,
DNUMBER Integer     NOT NULL,
MGR_SSN Integer     NOT NULL,
MGR_START_DATE  Date    NOT NULL,
CONSTRAINT DEPARTMENT_PK PRIMARY KEY (DNUMBER),
CONSTRAINT MGR_SSN_Relationship FOREIGN KEY(MGR_SSN)
REFERENCES EMPLOYEE(SSN)
);
I assume the error you're getting is due to trying to reference the DEPARTMENT table before it exists?

If so, then the solution is simple.


Create the EMPLOYEE table without the constraint that references the DEPARTMENT table;
Create the DEPARTMENT table as above;
Alter the EMPLOYEE table to add the constraint against the DEPARTMENT table.


Review the documentation on ALTER TABLE for how to use that command.

Given that there's no major changes needed once you know what the command is, here's a SQLFiddle link that shows you can create your tables if you pull the constraint against the then non-existent DEPARTMENT table out of CREATE TABLE EMPLOYEE, and add that constraint in using ALTER TABLE once both tables exist. (Click the "Build Schema" button to see that it builds successfully).



As pointed out by @a1ex07, you will probably want to initially make DEPARTMENT.MGR_SSN and EMPLOYEE.DNO NULL instead of NOT NULL; in fact, at least one will need to be NULL.

The problem would be that, initially, you have no rows in either table. You would be unable to insert a row into EMPLOYEE because there would be no valid value you could provide for DNO, and you'd be unable to insert a row into DEPARTMENT because you could provide no valid value for MGR_SSN.

Keeping in mind our above solution, you could


make DEPARTMENT.MGR_SSN allow NULL values initially
create the departments you need (with no managers)
add your employees (EMPLOYEE.DNO can be NOT NULL, as all the initial departments exist)
update DEPARTMENT with the appropriate MGR_SSN values
use ALTER TABLE to make DEPARTMENT.MGR_SSN NOT NULL.


Note, however, that you if you ever have a new department headed by a new employee, you'll need to:


Create the new employee tied to an existing department;
Create the new department, with the new employee as MGR_SSN;
Update the employee with the correct DNO.