MySQL ON UPDATE CASCADE级联更新简单场景报错原因排查

最小复现场景 #

建表语句 #

CREATE TABLE IF NOT EXISTS Table1 (
id          INTEGER NOT NULL,
PRIMARY KEY (id));

CREATE TABLE IF NOT EXISTS Table2 (
id        INTEGER NOT NULL,
name     VARCHAR(30) NOT NULL,
PRIMARY KEY (id, name),
FOREIGN KEY (id) REFERENCES Table1(id) on UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS Table3 (
id    INTEGER NOT NULL,
name  VARCHAR(30) NOT NULL,
date  DATE NOT NULL,
PRIMARY KEY (id, name, date),
FOREIGN KEY (id) REFERENCES Table1(id) on UPDATE CASCADE,
FOREIGN KEY (id, name) REFERENCES Table2(id, name) on UPDATE CASCADE);

测试数据 #

insert into Table1 (id) values (1);
insert into Table2 (id, name) values (1, "TEST");
insert into Table3 (id, name, date) values (1, "TEST", "2022-05-01");

触发报错的语句 #

UPDATE Table1 set id = 2 where id = 1;

报错信息 #

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
(`test`.`Table3`, CONSTRAINT `Table3_ibfk_1` FOREIGN KEY (`id`) REFERENCES `Table1` 
 (`id`) ON UPDATE CASCADE)

经测试，Table3无数据时上述更新语句可正常执行。 #

根本原因 #

这个报错是InnoDB引擎级联更新的实现逻辑，搭配子表重叠外键的设计共同导致的：

• 外键结构形成多路径级联：Table3上定义了两个外键，且两个外键共用id字段，因此从Table1的主键更新到Table3的级联存在两条独立路径：
  • 短路径：Table1 → Table3，通过单字段外键直接关联，级联时仅需要更新Table3的id值
  • 长路径：Table1 → Table2 → Table3，通过Table2的复合外键关联，级联时需要同时匹配id和name两个字段和Table2的记录一致
• InnoDB级联更新存在逻辑限制：InnoDB处理级联更新时采用广度优先遍历+逐行即时校验的规则，既不会等所有关联表的级联动作全部执行完成后再统一校验外键，也不会合并同一张子表上来自不同路径的级联更新动作：
  • 每更新完一行子表记录，就会立刻校验这行记录上所有外键约束是否满足，不会等待其他级联动作完成
  • 同一层级的级联动作（比如所有直接关联Table1的表的级联更新）会按顺序依次执行，执行顺序不保证符合业务上的外键依赖逻辑
• 中间状态触发约束校验失败：执行更新时的实际流程如下：
  1. 首先更新Table1的id从1改为2，完成父表更新
  2. 接下来处理所有直接关联Table1的第二层表（Table2、Table3）的级联动作，当轮到处理Table3的直连外键级联时，Table2的级联更新还未执行，Table2中仍只有(id=1, name='TEST')的记录
  3. 此时InnoDB尝试将Table3中关联记录的id从1改为2，记录临时变为(id=2, name='TEST', date='2022-05-01')，随即触发外键校验：该记录的复合外键需要匹配Table2中的(id,name)组合，但Table2中暂时不存在(2,'TEST')的记录，因此直接抛出外键约束错误
• Table3无数据时不存在需要级联更新的子记录，不会触发中间状态的校验，因此更新可以正常执行。

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