When I tried to execute the script, it says:
Error Code: 1824. Failed to open the referenced table 'department'
I was wondering if it has something to do with the way the tables are ordered when creating them? Or could it be that there are some values that I have entered wrongly?
What am I doing wrong here?
CREATE SCHEMA company;
USE company;
CREATE TABLE employee
(
EmpID INT NOT NULL,
EmpName VARCHAR(50) NOT NULL,
EmpGender CHAR(1) NOT NULL,
EmpAge INT NOT NULL,
EmpAddress VARCHAR(50) NOT NULL,
SuperID INT,
DeptID INT NOT NULL,
CONSTRAINT employee_pk PRIMARY KEY(EmpID),
CONSTRAINT employee_uk UNIQUE(EmpName),
CONSTRAINT employee_ck CHECK(EmpAge>18 AND EmpAge<100),
CONSTRAINT employee_fk1 FOREIGN KEY(SuperID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT employee_fk2 FOREIGN KEY(DeptID) REFERENCES department(DeptID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE department
(
DeptID INT NOT NULL,
DeptName VARCHAR(50) NOT NULL,
DeptBlock CHAR(1) NOT NULL,
DeptLevel INT NOT NULL,
ManagerID INT NOT NULL,
MStartDate DATE NOT NULL,
CONSTRAINT department_pk PRIMARY KEY(DeptID),
CONSTRAINT department_uk UNIQUE(DeptName),
CONSTRAINT department_ck CHECK(DeptBlock='A' OR DeptBlock='B'),
CONSTRAINT department_fk FOREIGN KEY(ManagerID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE project
(
ProjID INT NOT NULL,
ProjName VARCHAR(30) NOT NULL,
ProjStartDate DATE NOT NULL,
ProjBudget DECIMAL(6,2) NOT NULL,
DeptID INT NOT NULL,
CONSTRAINT project_pk PRIMARY KEY(ProjID),
CONSTRAINT project_uk UNIQUE(ProjName),
CONSTRAINT project_fk FOREIGN KEY(DeptID) REFERENCES department(DeptID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE work_on
(
EmpID INT NOT NULL,
ProjID INT NOT NULL,
StartDate DATE NOT NULL,
Hours_Worked INT NOT NULL,
CONSTRAINT wo_pk PRIMARY KEY(EmpID, ProjID),
CONSTRAINT wo_fk1 FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT wo_fk2 FOREIGN KEY(ProjID) REFERENCES project(ProjID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE dependent
(
EmpID INT NOT NULL,
DepName VARCHAR(50) NOT NULL,
DepGender CHAR(1) NOT NULL,
DepRelationship VARCHAR(20) NOT NULL,
CONSTRAINT dependent_pk PRIMARY KEY(EmpID, DepName),
CONSTRAINT dependent_fk FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE phone_no
(
EmpID INT NOT NULL,
Phone_No VARCHAR(15) NOT NULL,
CONSTRAINT phone_pk PRIMARY KEY(EmpID, Phone_No),
CONSTRAINT phone_fk FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT
);
The problem is the line CONSTRAINT employee_fk2 FOREIGN KEY(DeptID) REFERENCES department(DeptID) ON UPDATE CASCADE ON DELETE RESTRICT on employee table. You are trying to reference on an not existing table department.
Try the following order:
CREATE SCHEMA company;
USE company;
CREATE TABLE employee (
EmpID INT NOT NULL,
EmpName VARCHAR(50) NOT NULL,
EmpGender CHAR(1) NOT NULL,
EmpAge INT NOT NULL,
EmpAddress VARCHAR(50) NOT NULL,
SuperID INT,
DeptID INT NOT NULL,
CONSTRAINT employee_pk PRIMARY KEY(EmpID),
CONSTRAINT employee_uk UNIQUE(EmpName),
CONSTRAINT employee_ck CHECK(EmpAge>18 AND EmpAge<100),
CONSTRAINT employee_fk1 FOREIGN KEY(SuperID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE department (
DeptID INT NOT NULL,
DeptName VARCHAR(50) NOT NULL,
DeptBlock CHAR(1) NOT NULL,
DeptLevel INT NOT NULL,
ManagerID INT NOT NULL,
MStartDate DATE NOT NULL,
CONSTRAINT department_pk PRIMARY KEY(DeptID),
CONSTRAINT department_uk UNIQUE(DeptName),
CONSTRAINT department_ck CHECK(DeptBlock='A' OR DeptBlock='B'),
CONSTRAINT department_fk FOREIGN KEY(ManagerID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT
);
ALTER TABLE employee ADD CONSTRAINT employee_fk2 FOREIGN KEY(DeptID) REFERENCES department(DeptID) ON UPDATE CASCADE ON DELETE RESTRICT;
CREATE TABLE project (
ProjID INT NOT NULL,
ProjName VARCHAR(30) NOT NULL,
ProjStartDate DATE NOT NULL,
ProjBudget DECIMAL(6,2) NOT NULL,
DeptID INT NOT NULL,
CONSTRAINT project_pk PRIMARY KEY(ProjID),
CONSTRAINT project_uk UNIQUE(ProjName),
CONSTRAINT project_fk FOREIGN KEY(DeptID) REFERENCES department(DeptID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE work_on (
EmpID INT NOT NULL,
ProjID INT NOT NULL,
StartDate DATE NOT NULL,
Hours_Worked INT NOT NULL,
CONSTRAINT wo_pk PRIMARY KEY(EmpID, ProjID),
CONSTRAINT wo_fk1 FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT wo_fk2 FOREIGN KEY(ProjID) REFERENCES project(ProjID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE dependent (
EmpID INT NOT NULL,
DepName VARCHAR(50) NOT NULL,
DepGender CHAR(1) NOT NULL,
DepRelationship VARCHAR(20) NOT NULL,
CONSTRAINT dependent_pk PRIMARY KEY(EmpID, DepName),
CONSTRAINT dependent_fk FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE phone_no (
EmpID INT NOT NULL,
Phone_No VARCHAR(15) NOT NULL,
CONSTRAINT phone_pk PRIMARY KEY(EmpID, Phone_No),
CONSTRAINT phone_fk FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT
);