MySQL - Cannot add or update a child row: a foreign key constraint fails

Question

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

Answer 1

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; 

Answer 2

Since you haven't given table definitions, it's hard to guess. But it looks like you are attempting to modify the foreign key in the child table. AFAIK, this is illegal, you can modify it from the parent, but not the child table.

Consider this example:

CREATE TABLE parent (
  parent_id INT NOT NULL,
  parent_data int,

  PRIMARY KEY (parent_id)
) ENGINE=INNODB;

CREATE TABLE child1 (
  child1_id INT,
  child1_data INT,
  fk_parent_id INT,

  INDEX par_ind1 (fk_parent_id),

  FOREIGN KEY (fk_parent_id)
    REFERENCES parent(parent_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=INNODB;

CREATE TABLE child2 (
  child2_id INT,
  child2_data INT,
  fk_parent_id INT,

  INDEX par_ind2 (fk_parent_id),

  FOREIGN KEY (fk_parent_id)
    REFERENCES parent(parent_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=INNODB;

INSERT INTO parent
  (parent_id, parent_data)
  VALUES
  (1, 11),
  (2, 12);

INSERT INTO child1
  (child1_id, child1_data, fk_parent_id)
  VALUES
  (101, 1001, 1),
  (102, 1002, 1),
  (103, 1003, 1),
  (104, 1004, 2),
  (105, 1005, 2);

INSERT INTO child2
  (child2_id, child2_data, fk_parent_id)
  VALUES
  (106, 1006, 1),
  (107, 1007, 1),
  (108, 1008, 1),
  (109, 1009, 2),
  (110, 1010, 2);

Then this is allowed:

UPDATE parent
  SET parent_id = 3 WHERE parent_id = 2;

SELECT * FROM parent;
SELECT * FROM child1;
SELECT * FROM child2;

But this is not, because it modifies the parent fk from the child table:

UPDATE child1
  SET fk_parent_id = 4 WHERE fk_parent_id = 1;

It gets an error very similar to your error:

Cannot add or update a child row: a foreign key constraint fails (`db_2_b43a7`.`child1`, CONSTRAINT `child1_ibfk_1` FOREIGN KEY (`fk_parent_id`) REFERENCES `parent` (`parent_id`) ON DELETE CASCADE ON UPDATE CASCADE):