MySQL建表报错1824：无法打开引用表department问题咨询

• 核心是建表顺序不符合外键约束要求：MySQL要求创建外键时，被引用的表必须已经存在于当前库中。你先创建employee表时，其中employee_fk2外键要引用department表的DeptID字段，但此时department表还未创建，因此触发1824报错。
• 额外存在循环依赖问题：你的department表中也有外键department_fk引用employee表的EmpID字段，哪怕你调换两个表的创建顺序，先建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)
);

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

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

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

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

CREATE TABLE phone_no
(
EmpID INT NOT NULL,
Phone_No VARCHAR(15) NOT NULL,
CONSTRAINT phone_pk PRIMARY KEY(EmpID, Phone_No)
);

-- 所有表创建完成后，统一追加外键约束
ALTER TABLE employee ADD CONSTRAINT employee_fk1 FOREIGN KEY(SuperID) 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;
ALTER TABLE department ADD CONSTRAINT department_fk FOREIGN KEY(ManagerID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE project ADD CONSTRAINT project_fk FOREIGN KEY(DeptID) REFERENCES department(DeptID) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE work_on ADD CONSTRAINT wo_fk1 FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE work_on ADD CONSTRAINT wo_fk2 FOREIGN KEY(ProjID) REFERENCES project(ProjID) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE dependent ADD CONSTRAINT dependent_fk FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT;
ALTER TABLE phone_no ADD CONSTRAINT phone_fk FOREIGN KEY(EmpID) REFERENCES employee(EmpID) ON UPDATE CASCADE ON DELETE RESTRICT;

如果只是临时测试用，也可以在脚本开头添加SET FOREIGN_KEY_CHECKS = 0;，脚本末尾添加SET FOREIGN_KEY_CHECKS = 1;临时关闭外键检查，不需要调整原有脚本结构，但生产环境不推荐该操作，容易引入数据一致性问题。

内容的提问来源于stack exchange，提问作者Faith