MySQL中使用ALTER语句添加多个外键失败问题排查

问题场景 #

• 已创建emp_table（员工表）、dept_table（部门表）两张业务表
• 执行ALTER语句批量添加外键时，语句执行失败，无法创建外键约束

现有表结构 #

emp_table建表语句

-- Creating employee table
CREATE TABLE emp_table(
emp_id INT,
first_name VARCHAR(20),
last_name VARCHAR(20),
job_id VARCHAR(10),
salary INT,
manager_id INT,
dept_id INT,
PRIMARY KEY(emp_id));

dept_table建表语句

-- Creating department table
CREATE TABLE dept_table(
dept_id INT,
dept_name VARCHAR(40),
location VARCHAR(20),
manager_id INT,
relocation_id INT,
PRIMARY KEY(dept_name));

执行失败的SQL语句 #

-- Adding foreign keys
ALTER TABLE dept_table
ADD FOREIGN KEY (dept_id) REFERENCES emp_table(dept_id) ON DELETE SET NULL,
ADD FOREIGN KEY (manager_id) REFERENCES emp_table(manager_id) ON DELETE SET NULL;

失败诱因 #

1. 不满足外键的基础创建规则：MySQL规定，被外键引用的字段必须是被关联表的主键，或者配置了唯一约束的字段。当前语句引用的emp_table.dept_id、emp_table.manager_id两个字段既不是主键，也没有添加唯一索引，直接违反外键创建要求。
2. 字段属性和级联规则冲突：两个外键都配置了ON DELETE SET NULL（删除主表记录时从表关联字段置空）的级联规则，但dept_table中的dept_id、manager_id字段定义时没有显式声明允许为NULL，默认是NOT NULL非空属性，触发级联规则时会因为字段不能存空值报错。
3. 表结构和关联逻辑设计错误：
   • 主键设置不合理：dept_table将dept_name设为主键，部门名称存在修改、重名的可能，不适合作为主键，部门表主键应为无业务含义的dept_id。
   • 外键关联关系完全倒置：员工从属于部门，正确关联逻辑是emp_table.dept_id关联dept_table的主键dept_id，而非让部门表的dept_id引用员工表的非主键字段；部门负责人是员工，正确关联逻辑是dept_table.manager_id关联emp_table的主键emp_id，而非引用员工表中存储员工直属领导的manager_id字段。

修复方案 #

如果表中已有测试数据，先备份数据，再按以下步骤调整结构：

1. 删除原有错误表结构，重新定义符合设计规范的表，修正主键设置、字段非空属性：

-- 先删除有外键依赖的从表，再删除主表，避免删表报错
DROP TABLE IF EXISTS emp_table;
DROP TABLE IF EXISTS dept_table;

-- 创建部门表，dept_id作为主键，manager_id允许为空适配级联置空规则
CREATE TABLE dept_table(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(40) NOT NULL UNIQUE,
location VARCHAR(20),
manager_id INT NULL,
relocation_id INT
);

-- 创建员工表，emp_id作为主键，dept_id、manager_id允许为空适配级联置空规则
CREATE TABLE emp_table(
emp_id INT PRIMARY KEY,
first_name VARCHAR(20),
last_name VARCHAR(20),
job_id VARCHAR(10),
salary INT,
manager_id INT NULL,
dept_id INT NULL
);

2. 按正确的业务关联关系添加外键约束：

-- 给员工表添加部门关联外键
ALTER TABLE emp_table
ADD FOREIGN KEY (dept_id) REFERENCES dept_table(dept_id) ON DELETE SET NULL;

-- 给部门表添加部门负责人关联外键
ALTER TABLE dept_table
ADD FOREIGN KEY (manager_id) REFERENCES emp_table(emp_id) ON DELETE SET NULL;

-- 给员工表添加直属领导自关联外键
ALTER TABLE emp_table
ADD FOREIGN KEY (manager_id) REFERENCES emp_table(emp_id) ON DELETE SET NULL;

额外注意事项 #

• 外键关联的两个字段必须保证数据类型、字符集、排序规则、是否带无符号属性完全一致，否则也会导致外键创建失败。
• 添加外键前必须清理表内脏数据，如果从表中存在关联字段值在主表被引用字段中不存在的记录，需要先删除或修正这部分无效数据，再创建外键。

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