MySQL - Cannot add or update a child row: a foreign key constraint fails
foreign-keys, mysql, mysql-error-1452
Solution
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.
Problem
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 ```