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 (Dnumber) REFERENCES Department(Dnumber)
ALTER TABLE Project ADD
CONSTRAINT fk_Project FOREIGN KEY (Dnum) REFERENCES Department(Dnumber)
ALTER TABLE Works_on ADD
CONSTRAINT fk_WorksOn_Essn FOREIGN KEY (Essn) REFERENCES Employee(ssn),
CONSTRAINT fk_WorksOn_Pno FOREIGN KEY (Pno) REFERENCES Project(Pnumber)
Now when I'm trying to insert some data into those tables,
example:
INSERT INTO Employee(Fname, Minit, Lname, SSn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('John', 'B', 'Smith', '123456789', '1965-01-09', 'Lazy Town', 'M', 30000, '333445555', 5);
I realize that most of those tables requires foreign keys to any other tables, which makes the insertion very weird. To insert something, I must already have other data in the database. It's like vicious circle.
I have tried to insert some data for any of those tables, using those commends (one after another, not all at once). Every time it failed, because foreign keys can't find records in other tables :<
INSERT INTO Employee(Fname,Minit,Lname,SSn,Bdate,Address,Sex,Salary,Super_ssn,Dno) VALUES
('John','B','Smith','123456789', '1965-01-09', 'Lazy Town', 'M', 30000, '333445555',5);
INSERT INTO Dept_locations(Dnumber,Dlocation) VALUES
(1, 'Houston');
INSERT INTO Department(Dname,Dnumber,Mgr_ssn,Mgr_start_date) VALUES
('Research', 5, '333445555', '1988-05-22');
INSERT INTO Dependent(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES
('333445555', 'Alice', 'F', '1986-04-05', 'Daughter');
INSERT INTO Works_on(Essn,Pno,Hours) VALUES
('333445555', 3, 10);
INSERT INTO PROJECT(Pname,Pnumber,Plocation,Dnum) VALUES
('ProductX', 1, 'Bellaire', 5);
My question: is there any method to insert records in effective and safe way, without errors caused by non existing records?
Best Answer
Since
Department.Mgr_ssn
is nullable, I would insert the departments withoutMgr_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
.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.