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

User · Answer

I ve faced this issue and the solution was making sure that all the data from the child field are matching the parent field  for example  you want to add foreign key inside  attendance  table to the column  employeeName   where the parent is  employees  table   employeeName  column  all the data in attendance employeeName must be matching employee employeeName

User · Answer

My fix for this was my child table needed to be populated before the parent table  I had two tables  UserDetails and Login linked by an email address  I therefore had to insert into the UserDetails first before inserting into the Login table  insert into UserDetails  Email  Name  Telephone  Department  values   Email    Name    number    IT     Then  insert into Login  UserID  UserType  Email  Username  Password  VALUES  001   SYS-USR-ADMIN    Email    Name    Password

User · Answer

Such an error on update may be caused by the difference in character set and collation so make sure they are the same for both tables

User · Answer

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

User · Answer

I had the same problem but when I looked closely I found that  it was causing because I was trying to put the foreign key values into the tables before that key was assigned its primary key value  e g  I had two tables  customers  and  films    cust id  and  film id  were primary key respectively   customer  had one-to-many relation with  films  so I had  cust id  as foreign key in  films  tables  But I was trying to put values to  films  table first  so I got that problem

User · Answer

Hope this will assist anyone having the same error while importing CSV data into related tables  In my case the parent table was OK  but I got the error while importing data to the child table containing the foreign key  After temporarily removing the foregn key constraint on the child table  I managed to import the data and was suprised to find some of the values in the FK column having values of 0  obviously this had been causing the error since the parent table did not have such values in its PK column   The cause was that  the data in my CSV column preceeding the FK column contained commas  which I was using as a field delimeter   Changing the delimeter for my CSV file solved the problem

User · Answer

Even though this is pretty old  just chiming in to say that what is useful in  Sidupac s answer is the FOREIGN KEY CHECKS 0   This answer is not an option when you are using something that manages the database schema for you  JPA in my case  but the problem may be that there are  orphaned  entries in your table  referencing a foreign key that might not exist    This can often happen when you convert a MySQL table from MyISAM to InnoDB since referential integrity isn t really a thing with the former

User · Answer

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

User · Answer

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

User · Answer

I had faced same issue while creating foreign constraints on table  the simple way of coming out of this issue are first take backup of your parent and child table then truncate child table and again try to make a relation  hope this will solve the problem

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

Examples related to mysql

Examples related to foreign-keys

Examples related to mysql-error-1452