How do I insert records into database with foreign keys into SQL Server?

I'm writing a database in SQL Server, I have this schema

Basing on this, I've prepared UML diagram

I have written SQL code to create tables and alter them with foreign keys

```
CREATE TABLE Employee
(
Fname VARCHAR(20),
Minit CHAR(1),
Lname VARCHAR(30),
Ssn VARCHAR(9) CONSTRAINT pk_Employee PRIMARY KEY, -- pk key
Bdate DATE,
Address VARCHAR(30),
Sex CHAR(1),
Salary INT,
Super_ssn VARCHAR(9),
Dno INT,
CONSTRAINT chk_Employee_Ssn CHECK (LEN(Ssn)=9)
);

CREATE TABLE Department
(
Dname VARCHAR(30),
Dnumber INT CONSTRAINT pk_Department PRIMARY KEY, -- pk key
Mgr_ssn VARCHAR(9),
Mgr_start_date DATE,
CONSTRAINT chk_Department_MgrSsn CHECK (LEN(Mgr_ssn)=9)
);

CREATE TABLE Dept_locations
(
Dnumber INT,
Dlocation VARCHAR(30),
CONSTRAINT pk_Dept_locations PRIMARY KEY (Dnumber,Dlocation) -- pk key
);

CREATE TABLE Project
(
Pname VARCHAR(20),
Pnumber INT CONSTRAINT pk_Project PRIMARY KEY, -- pk key
Plocation VARCHAR(30),
Dnum INT
);

CREATE TABLE Works_on
(
Essn VARCHAR(9),
Pno INT,
Hours DECIMAL(7,2),
CONSTRAINT pk_WorksOn PRIMARY KEY (Essn,Pno), -- pk key
CONSTRAINT chk_WorksOn_Essn CHECK (LEN(Essn)=9)
);

CREATE TABLE Dependent
(
Essn VARCHAR(9) ,
Dependent_name VARCHAR(20),
Sex CHAR(1),
Bdate DATE,
Relationship VARCHAR(15),
CONSTRAINT pk_Dependent PRIMARY KEY (Essn,Dependent_name), -- pk key
CONSTRAINT chk_Dependent_Essn CHECK (LEN(Essn)=9)
);

ALTER TABLE Dependent ADD
CONSTRAINT fk_Dependent_Essn FOREIGN KEY (Essn) REFERENCES Employee(Ssn)

ALTER TABLE Department ADD
CONSTRAINT fk_Department_MgrSsn FOREIGN KEY (Mgr_ssn) REFERENCES Employee(Ssn)

ALTER TABLE Employee ADD
CONSTRAINT fk_Employee_SuperSsn FOREIGN KEY (Super_ssn) REFERENCES Employee(Ssn),
CONSTRAINT fk_Employee_Dno FOREIGN KEY (Dno) REFERENCES Department(Dnumber)

ALTER TABLE Dept_locations ADD
CONSTRAINT fk_DeptLocations_MgrSsn FOREIGN KEY (Dnumb

Since Department.Mgr_ssn is nullable, I would insert the departments without Mgr_ssn.

Then insert the employees as you did before. Start with the employee having no Super_ssn (the big boss, I guess). Then the emps whose super is the big boss, etc.

Finally update the departments to set the Mgr_ssn.

UPDATE Department
SET Mgr_ssn = '333445555'
WHERE Dnumber = 5

The rule is to first populate tables with a lookup character, i.e. tables containing constants. Departments are kind of constant, where as employees can change.

StackExchange Database Administrators Q#221762, answer score: 3