多外键指向同一表同列时更新用户ID触发外键约束失败问题

核心根因 #

报错的触发条件是你在user_map表中插入了user_a = user_b = 1的自关联映射记录，和MySQL外键级联更新的执行逻辑、约束校验时机产生了冲突：

1. 当你更新user表的id字段时，数据库会触发ON UPDATE CASCADE规则，依次更新user_map表中关联的user_a、user_b两个字段
2. 第一步更新完user_a为新id后，记录临时变为(新id, 旧id=1)，还未执行user_b的更新操作时，数据库会先触发外键约束校验：此时原user.id=1已经被修改为新值，user_b=1找不到对应的父表记录，直接触发外键校验失败报错
3. 删除操作无异常的原因是：ON DELETE CASCADE是直接删除整条user_map关联记录，不需要分字段更新，不存在中间状态的约束冲突。

可落地解决方案 #

你可以根据业务场景选择以下任意一种方案：

• 业务层面禁止插入user_a = user_b的自关联映射记录，这类自关联映射在绝大多数用户关系场景下没有实际业务意义，去掉这类记录后更新操作即可正常执行
• 如果必须保留自关联映射逻辑，可移除外键级联更新规则，改为业务代码手动处理更新逻辑：先更新user表的id值，再批量更新user_map表的user_a、user_b字段，业务层面自行保证数据一致性
• 如果不需要双向关系去重逻辑，可删除user_map表的other_way联合唯一索引，可降低非自关联场景下的更新冲突概率

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