创建关联表时遇外键约束错误，求解决（附两张表SQL）

错误原因 #

你遇到的foreign key constraints is incorrectly formed错误有两个核心问题：

1. 外键方向逻辑颠倒：部门表（dept_name）是主表，员工表（Employee）是从表，应该是员工表的dept_id关联部门表的主键dep_id，而非让部门表的dep_id关联员工表的dept_id。
2. 被关联字段缺少索引：MySQL要求外键引用的字段必须是主键或唯一索引，原Employee表的dept_id既不是主键也无唯一索引，无法满足外键约束的要求。

正确的表创建方案 #

步骤1：先创建部门主表 #

部门表作为主表，先定义主键，确保后续员工表可以正常关联：

create table if not exists dept_name (
    dep_id int not null,
    dept_name varchar(55) default null,
    dept_block varchar(55) default null,
    constraint pk_dept primary key(dep_id)
);

步骤2：创建员工从表并添加外键 #

员工表的dept_id作为外键，关联部门表的主键dep_id：

create table if not exists Employee (
    id int not null auto_increment,
    name varchar(55) default null,
    dept_id int default null,
    birth text default null,
    primary key (`id`),
    -- 添加外键约束，关联部门表主键
    constraint fk_employee_dept foreign key(dept_id) references dept_name(dep_id)
);

已存在表的修复方案 #

如果已经创建了Employee表，不想删除重建，可以按以下步骤操作：

1. 先给Employee表的dept_id添加索引：

create index idx_employee_dept_id on Employee(dept_id);

2. 再添加外键约束：

alter table Employee add constraint fk_employee_dept foreign key(dept_id) references dept_name(dep_id);

验证JOIN查询 #

表创建完成后，即可正常执行JOIN查询，比如查询员工及其所属部门信息：

select e.id, e.name, d.dept_name, d.dept_block
from Employee e
left join dept_name d on e.dept_id = d.dep_id;

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