建表与外键关联：先插数据还是先设置？外键报错解析

报错原因 #

你遇到的ER_NO_REFERENCED_ROW_2错误，核心原因是employee表中已存在的dept_id值，在department表中找不到匹配的记录。

外键约束的本质是强制子表（employee）的关联字段（dept_id）的所有值，必须在父表（department）的对应字段（dept_id）中存在。你先往employee插入了D1、D2、D10这些dept_id，但此时department表要么不存在，要么没有这些值，所以添加外键时MySQL检查现有数据不符合约束，直接报错。而空表时没有数据需要检查，所以外键能正常创建。

完整操作步骤 #

要正确创建带外键关联的表，必须遵循先创建父表→创建子表（或先创建所有表再添加外键，但要保证父表数据先于子表插入）→插入父表数据→插入子表数据的顺序，具体如下：

1. 创建所有基础表（先创建被引用的父表） #

-- 先创建department表（被employee和manager引用的父表）
CREATE TABLE department (
    dept_id VARCHAR(40) PRIMARY KEY,
    dept_name VARCHAR(40) -- 可根据实际需求调整字段
);

-- 创建manager表（被employee引用）
CREATE TABLE manager (
    man_id VARCHAR(40) PRIMARY KEY,
    man_name VARCHAR(40),
    dept_id VARCHAR(40),
    -- 给manager的dept_id添加外键，关联department
    FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON DELETE SET NULL
);

-- 创建employee表，直接定义外键约束
CREATE TABLE employee (
    emp_id VARCHAR(40) PRIMARY KEY,
    emp_name VARCHAR(40),
    salary VARCHAR(40),
    dept_id VARCHAR(40),
    man_id VARCHAR(40),
    FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON DELETE SET NULL,
    FOREIGN KEY (man_id) REFERENCES manager(man_id) ON DELETE SET NULL
);

2. 插入父表数据（先插入department，再插入manager） #

-- 插入部门数据，覆盖employee和manager中用到的所有dept_id
INSERT INTO department VALUES ('D1', '研发部');
INSERT INTO department VALUES ('D2', '市场部');
INSERT INTO department VALUES ('D3', '人事部');
INSERT INTO department VALUES ('D4', '财务部');
INSERT INTO department VALUES ('D10', '运维部');

-- 插入经理数据
INSERT INTO manager VALUES ('M1', 'Prem', 'D3');
INSERT INTO manager VALUES ('M2', 'Shripadh', 'D4');
INSERT INTO manager VALUES ('M3', 'Nick', 'D1');
INSERT INTO manager VALUES ('M4', 'Cory', 'D1');

3. 插入子表（employee）数据 #

INSERT INTO employee VALUES ('E1', 'Rahul', '15000', 'D1', 'M1');
INSERT INTO employee VALUES ('E2', 'Manoj', '15000', 'D1', 'M1');
INSERT INTO employee VALUES ('E3', 'James', '55000', 'D2', 'M2');
INSERT INTO employee VALUES ('E4', 'Michael', '25000', 'D2', 'M2');
INSERT INTO employee VALUES ('E5', 'Ali', '20000', 'D10', 'M3');
INSERT INTO employee VALUES ('E6', 'Robin', '35000', 'D10', 'M3');

可选：分步创建表后添加外键 #

如果习惯先建表再单独配置外键，需保证添加外键前，父表已存在且包含子表关联字段的所有值：

-- 1. 先创建所有无外键的表
CREATE TABLE department (
    dept_id VARCHAR(40) PRIMARY KEY,
    dept_name VARCHAR(40)
);

CREATE TABLE manager (
    man_id VARCHAR(40) PRIMARY KEY,
    man_name VARCHAR(40),
    dept_id VARCHAR(40)
);

CREATE TABLE employee (
    emp_id VARCHAR(40) PRIMARY KEY,
    emp_name VARCHAR(40),
    salary VARCHAR(40),
    dept_id VARCHAR(40),
    man_id VARCHAR(40)
);

-- 2. 插入父表数据
INSERT INTO department VALUES ('D1', '研发部'), ('D2', '市场部'), ('D3', '人事部'), ('D4', '财务部'), ('D10', '运维部');
INSERT INTO manager VALUES ('M1', 'Prem', 'D3'), ('M2', 'Shripadh', 'D4'), ('M3', 'Nick', 'D1'), ('M4', 'Cory', 'D1');

-- 3. 添加外键约束
ALTER TABLE manager ADD FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON DELETE SET NULL;
ALTER TABLE employee ADD FOREIGN KEY (dept_id) REFERENCES department(dept_id) ON DELETE SET NULL;
ALTER TABLE employee ADD FOREIGN KEY (man_id) REFERENCES manager(man_id) ON DELETE SET NULL;

-- 4. 插入employee数据
INSERT INTO employee VALUES ('E1', 'Rahul', '15000', 'D1', 'M1'), ('E2', 'Manoj', '15000', 'D1', 'M1'), ('E3', 'James', '55000', 'D2', 'M2'), ('E4', 'Michael', '25000', 'D2', 'M2'), ('E5', 'Ali', '20000', 'D10', 'M3'), ('E6', 'Robin', '35000', 'D10', 'M3');

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