MySQL錯誤代碼：1824。無法打開參考的表“部門”

當我嘗試執行腳本時，它說：

錯誤代碼：1824。無法打開參考的表“部門”

我想知道它是否與創建表時的排序方式有關？或者可能是我輸入了錯誤的某些值？

我在這里做錯了什么？

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

uj5u.com熱心網友回復：

問題是桌子CONSTRAINT employee_fk2 FOREIGN KEY(DeptID) REFERENCES department(DeptID) ON UPDATE CASCADE ON DELETE RESTRICT上的線employee。您正在嘗試參考不存在的表department。

嘗試以下順序：

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

演示：https : //www.db-fiddle.com/f/qff694udysNgqbyJyFcDzn/2

標籤：mysql 数据库 错误代码

上一篇：為什么FileAge回傳意外值？

下一篇：如何修復這些單元測驗？