MySQL含自引用外键的表记录更新失败问题求助

可行解决方案 #

方案1：临时关闭外键检查（最简单直接） #

执行以下SQL语句，先关闭外键约束检查，完成更新后再恢复：

SET FOREIGN_KEY_CHECKS = 0;
UPDATE categories SET id = 3 WHERE id = 1;
SET FOREIGN_KEY_CHECKS = 1;

执行后两条记录的parent_id会自动通过ON UPDATE CASCADE更新为3，满足需求。

方案2：调整更新顺序（不关闭外键约束） #

如果不想关闭外键检查，可以通过临时修改自引用的parent_id来规避冲突：

-- 先把record1的parent_id改为已存在的id（比如id=2）
UPDATE categories SET parent_id = 2 WHERE id = 1;
-- 更新id=1为3
UPDATE categories SET id = 3 WHERE id = 1;
-- 把record1的parent_id改回3，此时record2的parent_id会自动同步为3
UPDATE categories SET parent_id = 3 WHERE id = 3;

报错原因 #

出现这个错误是因为InnoDB处理自引用外键的CASCADE更新时，约束检查优先级高于CASCADE操作。当你尝试将id=1改为3时，数据库会先检查所有引用id=1的parent_id字段——此时record1自身的parent_id还是1，而id=1正被修改，数据库会判定这个引用的id不存在，直接触发外键约束错误，CASCADE更新还没来得及执行。

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