Skip to content
Eleven52AC Blog

外键约束专栏

关系型数据库的根价值是引用完整性：子表中的“引用”必须能在父表里找到对应“被引用”的记录。外键就是把这条业务不变量固化在数据库层，让“错误数据根本写不进去”。有了它，系统天然避免了“孤儿记录”和“脏引用”，在做删除、统计、审计时不再需要到处兜底。代价也真实存在：每一次对子表外键列的插入或更新，都要去父表做存在性校验；删除父表记录时，还要处理子表是否允许继续引用或如何级联，这些都会带来额外的索引访问、加锁与等待。

1. 外键的规则与要点

Section titled “1. 外键的规则与要点”

在 MySQL 8.x 的 InnoDB 引擎里，外键有几条必须遵守的硬规则。忽视它们，ALTER TABLE … ADD CONSTRAINT 多半以 ERROR 1215/1216 告终。

  1. 被引用列必须可唯一定位。父表被引用的列，必须是 PRIMARY KEY 或带有 UNIQUE 约束（可以是复合列）。

  2. 列定义必须一致。父子两边的外键列在数据类型、长度、无符号属性、字符集与排序规则（对字符型）上必须一致，INT 对 BIGINT、utf8mb4_general_ci 对 utf8mb4_unicode_ci 都不行。

  3. 索引要求。父表被引用列必须有唯一索引；子表引用列也需要索引以支撑校验与删除检查，InnoDB 会在缺失时自动创建（为了可读可控，工程上建议显式建索引并自行命名）。

  4. 检查时机不可延迟。MySQL 不支持 DEFERRABLE，外键检查永远是立即（immediate） 的：语句还没提交就要通过检查。

  5. NO ACTION 等价于 RESTRICT。MySQL 不实现 SQL 标准里的延迟行为，NO ACTION 与 RESTRICT 同义。SET DEFAULT 语义也被解析但不生效（等价于 RESTRICT），因此不要在 DDL 里写 SET DEFAULT。

  6. FOREIGN_KEY_CHECKS 只是跳过校验。把它设为 0 可以临时忽略检查、批量导入，但不会帮你修正数据；重新打开检查时，违反约束的 DML 仍会失败。

基本语法如下，注意命名约束，避免默认名不友好：

CREATE TABLE department (
  dept_id   BIGINT PRIMARY KEY,
  dept_name VARCHAR(64) NOT NULL
) ENGINE=InnoDB;


CREATE TABLE employee (
  emp_id    BIGINT PRIMARY KEY,
  emp_name  VARCHAR(64) NOT NULL,
  dept_id   BIGINT,
  CONSTRAINT fk_emp_dept
    FOREIGN KEY (dept_id)
    REFERENCES department (dept_id)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
) ENGINE=InnoDB;

2. ON DELETE / ON UPDATE

Section titled “2. ON DELETE / ON UPDATE”

理解这两个子句，等于理解外键 80% 的“动作学”。它们定义了“父表变化，子表怎么办”。

  • RESTRICT / NO ACTION：如果存在引用，父表的删除/更新就会被拒绝。这是最安全也最常用的默认选择，能从根上防止脏引用。适合大多数不可随意删除、不可改主键的业务主表。

  • CASCADE：父表的删除/更新会传递到子表。删除会把所有子记录一并删掉，更新会同步改子表外键值。适合“短生命周期、严格隶属”的从属关系（如“订单头—订单行”，但前提是量不大且可控）。

  • SET NULL：父表删除/更新后，子表外键列置空，要求子表外键列可为 NULL。适合“引用关系可暂时悬空”的业务（例如员工离职后其历史绩效保留，但不再指向现存部门）。

  • SET DEFAULT：MySQL 实际上不支持，写了也相当于 RESTRICT。不要使用。

工程取舍建议是：更新用 RESTRICT，删除根据从属强度选 RESTRICT / SET NULL / CASCADE。ON UPDATE CASCADE 只在你真的使用“可变的自然键”时有价值（多数系统都用不变的代理键，自然不会更改父键）。

3. 外键对并发与锁的影响

Section titled “3. 外键对并发与锁的影响”

外键校验不是纯计算，它涉及索引访问与行锁。InnoDB 的行为大致是：

  • 子表插入或更新外键列：会根据父表唯一索引去做存在性检查，对命中的父记录加记录锁/间隙锁，以保证检查结果在当前事务中不被破坏。

  • 父表删除或更新被引用的键：会检查子表是否存在引用；RESTRICT 会直接阻止；CASCADE/SET NULL 会发起对子表的批量 DML，若量大，容易形成长事务，并扩大锁定范围。

  • 交叉操作导致死锁：会话 A 在子表插入等待父表锁，另一个会话 B 在父表删除等待子表锁，便可能出现经典死锁，最终其中一个回滚。

降低风险的三件事最有效：

  1. 给外键两侧列都建好合适的索引，让检查与清理都走快速等值；
  2. 固定 DML 顺序（先父后子，删时先子后父）；
  3. 在热点特别严重的链路上，用“逻辑外键”替代物理外键：应用层在同一事务中先查父表存在性再写子表，同时在子表外键列保留普通索引以支持清理和约束性查询。这是高并发系统里的常见平衡方案。

4. 什么时候必须上 FK，什么时候建议逻辑 FK

Section titled “4. 什么时候必须上 FK，什么时候建议逻辑 FK”

外键的价值在强一致 + 合规审计场景最为突出，例如资金、合同、凭证、审批链这些不可容忍脏引用的核心域，这些表的数据量也相对可控、并发可管理，建议保留 FK。
对于写入风暴的明细表、日志表、埋点流，以及热点父表（每秒成百上千次引用校验），物理 FK 更容易放大锁与等待。此时常见做法是：取消 FK → 保留索引 → 应用校验 + 定期巡检修复，把锁粒度与顺序掌控在业务代码里。

5. 对生产库添加外键流程

Section titled “5. 对生产库添加外键流程”

真实库里经常有历史脏数据，贸然 ADD CONSTRAINT 会直接失败或长时间卡住。建议走一套“治理 → 预演 → 可回退”的流程。

  1. 离线治理：先找潜在冲突，再清理。

    -- 即将加 FK(employee.dept_id -> department.dept_id)
    -- 1）找孤儿记录（子有父无）
    SELECT e.dept_id, COUNT(*) cnt
    FROM employee e
    LEFT JOIN department d ON d.dept_id = e.dept_id
    WHERE e.dept_id IS NOT NULL AND d.dept_id IS NULL
    GROUP BY e.dept_id;
    

    -- 2）确认父表唯一性是否满足（如果准备引用非 PK 列）
    SELECT dept_code, COUNT(*) FROM department GROUP BY dept_code HAVING COUNT(*) > 1;

  2. 先建索引再加约束：

    ALTER TABLE employee
      ADD INDEX idx_emp_dept (dept_id);  -- 子表索引
    -- 父表列需是 PK 或 UNIQUE

  3. 在影子/从库预演时长与阻塞，必要时使用在线 DDL：

    ALTER TABLE employee
      ALGORITHM=INPLACE, LOCK=NONE
      ADD CONSTRAINT fk_emp_dept
      FOREIGN KEY (dept_id) REFERENCES department(dept_id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;

    对于超大表或老版本能力不足，考虑 pt-online-schema-change。

  4. 发布窗口内控制写入（降速或暂停受影响的写链路），并准备好回滚 SQL（ALTER TABLE … DROP FOREIGN KEY …）。

  5. 上线后观察锁等待、死锁次数、回滚率与慢日志；必要时回退或转逻辑 FK。

6. 外键相关的常见错误

Section titled “6. 外键相关的常见错误”

  • ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
    典型是插入/更新子表时，父表不存在对应记录；或 ON DELETE SET NULL 目标列不可为 NULL。排查维度：父键是否存在、值是否精确相等（含字符集排序规则）、子表列是否允许空。

  • ERROR 1215 (HY000): Cannot add foreign key constraint / 1216
    多见于 DDL 加约束失败。逐一核对：父表是否 PK/UNIQUE、列类型/长度/字符集是否一致、两边索引是否满足、现存数据是否有孤儿、表引擎是否都是 InnoDB。

  • 死锁频发
    抓取 SHOW ENGINE INNODB STATUS 或开启死锁日志，定位是“父删子删”还是“子插父删”的交叉顺序；随后修订 DML 顺序、批量删分片化、或改为逻辑 FK。

辅助排查 SQL（找孤儿 / 找引用）：

-- 子表中引用了不存在的父表键
SELECT f.business_villa_housing_id, COUNT(*)
FROM zggf_family f
LEFT JOIN zggf_business_villa_housing h
  ON h.id = f.business_villa_housing_id
WHERE f.business_villa_housing_id IS NOT NULL
  AND h.id IS NULL
GROUP BY f.business_villa_housing_id;


-- 父表是否被任何子表引用（删除前自检）
SELECT 1
FROM zggf_family
WHERE business_villa_housing_id = :id
LIMIT 1;