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
I solved my 'foreign key constraint fails' issues by adding the following code to the start of the SQL code (this was for importing values to a table)
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;
Then adding this code to the end of the file
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;