MySQL - 无法添加或更新子行:外键约束失败 [英] MySQL - Cannot add or update a child row: a foreign key constraint fails
问题描述
我在MySQL中有一组InnoDB用户表通过外键捆绑在一起;父$ 用户
表和一组存储电子邮件地址,动作等的子表。这些都与父 user $ (code> int(10)
), >
所有的子表都有一个 uid
值,外键约束指向 user.uid
,并设置为 ON DELETE CASCADE
和 ON UPDATE CASCADE
。
当我从 user
删除一个用户时,所有的子约束条目都被删除。但是,当我尝试更新 user.uid
值时,会导致以下错误,而不是级联 uid
更改为子表:
#1452 - 无法添加或更新子行:外键约束失败`.`user_email`,CONSTRAINT`user_email_ibfk_2` FOREIGN KEY(`uid`)REFERENCES`user`(`uid`)ON DELETE CASCADE ON UPDATE CASCADE)
我有一种感觉,我必须在这里忽略一些明显的东西。使用 user_email
删除键约束并尝试更新 user
中的值会导致相同的错误,但会导致下一个字母 user
子表,所以我不相信这是一个表特定的错误。
编辑: p>
将结果添加到 SHOW ENGINE INNODB STATUS
:
------------------------
最新的外键错误
---- --------------------
121018 22:35:41 Transaction:
TRANSACTION 0 5564387,ACTIVE 0秒,进程号1619,操作系统线程id 2957499248更新或删除,在InnoDB中声明的线程499
正在使用的mysql表1,锁定1
17锁结构,堆大小2496,9行锁,撤销日志条目2
MySQL线程ID 3435659,查询ID 24068634 localhost root更新
UPDATE`accounts`.`user` SET`uid` ='1'WHERE`user`.`uid` = 306
外键约束失败表`accounts`.`user_email`:
,
CONSTRAINT`us尝试添加子表中的索引uid元组:
DATA TUPLE:2();
试图在子表中添加子元素:
DATA TUPLE:2领域;
...
一串十六进制代码
但是在父表'accounts`.`user`中,在索引`PRIMARY`中,
与我们最接近的匹配可以找到记录:
...
一串十六进制代码
在一个不相关的任务上,我最近在 MySQL中引入了我们的MySQL数据库工作台,当查看上表的关系时,我注意到重复和/或虚假的关系,我以前不知何故错过了(他们没有在PHPMyAdmin FWIW中显示)。删除这些额外的关系立即解决了这个问题。
This seems to be a common error, but for the life of me I can't figure this out.
I have a set of InnoDB user tables in MySQL that are tied together via foreign key; the parent user
table, and a set of child tables that store email addresses, actions, etc. These are all tied to the parent user
table by a foreign key, uid
, with all of the parent and child keys being int(10)
.
All of the child tables have a uid
value with a foreign key constraint pointing to user.uid
, and set to ON DELETE CASCADE
and ON UPDATE CASCADE
.
When I delete a user from user
, all of the child constrained entries are removed. However, when I attempt to update a user.uid
value, it results in the following error, rather than cascading the uid
change to the child tables:
#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)
I have a feeling I must be missing something obvious here. Removing the key constraint with user_email
and attempting to update the value in user
results in the same error but for the next alphabetical user
child table, so I don't believe it is a table-specific error.
EDIT:
Adding in the results from 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
On an unrelated task, I recently brought up our MySQL database in MySQL Workbench, and when viewing the table relations for the above tables, I noticed 'duplicate' and/or spurious relations that I had somehow missed before (they weren't showing up in PHPMyAdmin FWIW). Removing these extra relations cleared up the issue immediately.
这篇关于MySQL - 无法添加或更新子行:外键约束失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!