SQL 外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。带有外键的表叫子表（从表），被引用的表叫父表（主表），例如员工表 emp 的 dept_id 指向部门表 dept 的 id。

只在子表里存了 dept_id 字段、没有建立外键关联时，数据库层面是无法保证数据一致性与完整性的（例如直接删掉部门表里 id = 1 的记录，员工表中的相关记录就成了脏数据）。

一、语法

-- 1. 创建表时添加外键
CREATE TABLE 表名(
    字段名 数据类型,
    ...
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

-- 2. 建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);

-- 3. 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

二、准备数据与示例

-- 父表：部门表
CREATE TABLE dept(
    id    INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    name  VARCHAR(50) NOT NULL COMMENT '部门名称'
) COMMENT '部门表';

INSERT INTO dept (id, name) VALUES (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办');

-- 子表：员工表
CREATE TABLE emp(
    id         INT AUTO_INCREMENT COMMENT 'ID' PRIMARY KEY,
    name       VARCHAR(50) NOT NULL COMMENT '姓名',
    age        INT COMMENT '年龄',
    job        VARCHAR(20) COMMENT '职位',
    salary     INT COMMENT '薪资',
    entrydate  DATE COMMENT '入职时间',
    managerid  INT COMMENT '直属领导ID',
    dept_id    INT COMMENT '部门ID'
) COMMENT '员工表';

INSERT INTO emp (id, name, age, job, salary, entrydate, managerid, dept_id) VALUES
(1, '金庸', 66, '总裁', 20000, '2000-01-01', NULL, 5),
(2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
(5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
(6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);

-- 添加外键
ALTER TABLE emp ADD CONSTRAINT fk_dept_emp_id FOREIGN KEY (dept_id) REFERENCES dept (id);

-- 删除外键
ALTER TABLE emp DROP FOREIGN KEY fk_dept_emp_id;

三、删除 / 更新行为

行为	说明
`NO ACTION`	在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与 `RESTRICT` 一致）
`RESTRICT`	同上，与 `NO ACTION` 一致
`CASCADE`	在父表中删除/更新对应记录时，如果有对应外键，则级联删除/更新子表中的相关记录
`SET NULL`	在父表中删除对应记录时，如果有对应外键，则把子表中该外键值置为 null（要求该外键允许取 null）
`SET DEFAULT`	父表有变更时，子表把外键列设置成一个默认值（InnoDB 不支持）

-- 级联更新 + 级联删除
ALTER TABLE emp ADD CONSTRAINT fk_dept_emp_id FOREIGN KEY (dept_id) REFERENCES dept (id)
    ON UPDATE CASCADE ON DELETE CASCADE;

-- 父表变更时把子表外键置空
ALTER TABLE emp ADD CONSTRAINT fk_dept_emp_id FOREIGN KEY (dept_id) REFERENCES dept (id)
    ON UPDATE SET NULL ON DELETE SET NULL;

不显式指定行为时默认是 NO ACTION/RESTRICT，即父表记录被子表引用时不允许删除。

四、速查

-- 添加外键（建表时）
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)

-- 添加外键（建表后）
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名);

-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

-- 指定删除/更新行为
... REFERENCES 主表 (主表列名) ON UPDATE CASCADE ON DELETE CASCADE;