3

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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