Home > front end >  MySQL Error Code: 1824. Failed to open the referenced table 'department'
MySQL Error Code: 1824. Failed to open the referenced table 'department'

Time:11-05

Can anyone tell me what I am doing wrong here?

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?

Please help me, I've been stuck on this for hours! >_<

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
);

CodePudding user response:

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
                     );

Demo: https://www.db-fiddle.com/f/qff694udysNgqbyJyFcDzn/2

  • Related