MySQL - 无法添加或更新子行 : a foreign key constraint fails

Question

这似乎是一个常见的错误，但我这辈子都想不通。

我在 MySQL 中有一组 InnoDB 用户表，它们通过外键绑定(bind)在一起；父 user 表和一组存储电子邮件地址、操作等的子表。这些都通过外键 绑定(bind)到父 user 表>uid，所有的父键和子键都是int(10)。

所有子表都有一个 uid 值，外键约束指向 user.uid，并设置为 ON DELETE CASCADE和 ON UPDATE CASCADE。

当我从 user 中删除用户时，所有子约束条目都将被删除。但是，当我尝试更新 user.uid 值时，会导致以下错误，而不是将 uid 更改级联到子表:

#1452 - Cannot add or update a child row: a foreign key constraint fails (`accounts`.`user_email`, CONSTRAINT `user_email_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE)

我有一种感觉，我必须在这里遗漏一些明显的东西。使用 user_email 删除键约束并尝试更新 user 中的值会导致相同的错误，但对于下一个按字母顺序排列的 user 子表，因此我不认为这是特定于表的错误。

编辑:

添加 SHOW ENGINE INNODB STATUS 的结果:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
121018 22:35:41 Transaction:
TRANSACTION 0 5564387, ACTIVE 0 sec, process no 1619, OS thread id 2957499248 updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
17 lock struct(s), heap size 2496, 9 row lock(s), undo log entries 2
MySQL thread id 3435659, query id 24068634 localhost root Updating
UPDATE `accounts`.`user` SET `uid` = '1' WHERE `user`.`uid` = 306
Foreign key constraint fails for table `accounts`.`user_email`:
,
  CONSTRAINT `user_email_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index `uid` tuple:
DATA TUPLE: 2 fields;
...
A bunch of hex code

But in parent table `accounts`.`user`, in index `PRIMARY`,
the closest match we can find is record:
...
A bunch of hex code

Answer 1

我通过将以下代码添加到 SQL 代码的开头解决了我的“外键约束失败”问题(这是用于将值导入表)

SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0; 

然后将此代码添加到文件末尾

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;