Cannot add or update a child row a foreign key constraint fails

Question

table 1   ---------- ------------- ------ ----- --------- ----------------    Field      Type          Null   Key   Default   Extra             ---------- ------------- ------ ----- --------- ----------------    UserID     int 11        NO     PRI   NULL      auto increment     Password   varchar 20    NO                                        Username   varchar 25    NO                                        Email      varchar 60    NO                                       ---------- ------------- ------ ----- --------- ----------------    table2   ------------------ -------------- ------ ----- --------- ----------------    Field              Type           Null   Key   Default   Extra             ------------------ -------------- ------ ----- --------- ----------------    UserID             int 11         NO     MUL                                PostID             int 11         NO     PRI   NULL      auto increment     Title              varchar 50     NO                                        Summary            varchar 500    NO                                       ------------------ -------------- ------ ----- --------- ----------------    Error   com mysql jdbc exceptions MySQLIntegrityConstraintViolationException   Cannot add or update a child row  a foreign key constraint fails    myapp table2   CONSTRAINT  table2 ibfk 1  FOREIGN KEY   UserID    REFERENCES  table1    UserID       What have I done wrong  I read http   www w3schools com Sql sql foreignkey asp and I don t see what s wrong

User · Answer

I also got this error   Cannot add or update a child row  a foreign key constraint fails   I got the error when adding a new row to the parent table  The problem was that the foreign key constraint had been defined on the parent table instead of the child table

User · Answer

A simple hack can be to disable foreign key checks before performing any operation on the table  Simply query SET FOREIGN KEY CHECKS 0  This will disable foreign key matching against any other tables  After you are done with the table enable it again SET FOREIGN KEY CHECKS 1  This works for me a lot of times   Please note that you enter the DANGER ZONE when you do this  While there are certainly valid use cases  you should only do this when you are certain you understand the implications

User · Answer

Is there any existing data that the table contains  If so  try to clear out all the data in the table you want to add a foreign key  Then run the code  add a foreign key  again   I encountered this problem so many times  This clearing out the all data in the table works when you want to add foreign key on a existing table   Hope this works

User · Answer

This took me a while to figure out  Simply put  the table that references the other table already has data in it and one or more of its values does not exist in the parent table   e g  Table2 has the following data   UserID    PostID    Title    Summary 5         1         Lorem    Ipsum dolor sit   Table1  UserID    Password    Username    Email 9                     JohnDoe     john example com   If you try to ALTER table2 and add a foreign key then the query will fail because UserID 5 doesn t exist in Table1

User · Answer

If you use mysql index or relation between tables  firstly you delete the colums for example city id  and create new colums with same name for example city id  Then try again

User · Answer

Delete indexes of the UserID field of table2  Its suits for me

User · Answer

You re getting this error because there are some value int table2 UserID that is not exists on table1 UserID  I guess that you have setted table2 UserID value manualy before you created this foreign key    One example for this scene  table1 UserID get values 1 2 3 and table2 UserID get values 4  add by manual   So when you make a foreign key  they can t find UserID   4 from table1 and the error will ocurse   To fix this error  just remove UserID   4 from table2 or you can empty both of them and then create the foreign key and  Good luck

User · Answer

Make sure you have set database engine to InnoDB because in MyISAM foreign key and transaction are not supported

User · Answer

You should not put an ondelete field against a cascade in the database   So set the onDelete field to RESTRICT  Good luck

User · Answer

I discovered another weird case  If you accidentally create a foreign key from an InnoDB table to a MyISAM table  MySQL throws this error at time of insert even if the data is otherwise valid   See http   nick zoic org art mysql-foreign-key-error

User · Answer

Just a little bit fix   Make the JoinColumn  nullable   true  in Table1 and  UserID  field  insertable false  and  nullable true  in Table2   In Table1 Entity      OneToMany targetEntity Table2 class  cascade   CascadeType ALL   JoinColumn name    UserID   referencedColumnName    UserID   nullable   true  private List lt Table2 gt  table2List    In Table2 Entity    Column insertable   false  nullable   true  private int UserID

User · Answer

That error occurs when you want to add a foreign key with values that don t exist in the primary key of the parent table  You must be sure that the new foreign key UserID in table2 has values that exist in the table1 primary key  sometimes by default it is null or equal to 0  You could first update all the fields of the foreign key in table2 with a value that exists in the primary key of table1  update table2 set UserID   1 where UserID is null  If you want to add different UserIDs you must modify each row with the values you want

User · Answer

If you have inserted a row into table 1 before creating the foreign key in table 2  then you will get a foreign key constraint error  because the auto increment value is 2 in table 1 and 1 in table 2  To solve this you have to truncate table 1 and set the auto increment value back to 1  Then you can add table 2

User · Answer

I also faced same issue and the issue was my parent table entries value not match with foreign key table value  So please try after clear all rows

User · Answer

Maybe whilst you added the userID column  there  is a data for that certain table that it is established so it will have a default value of 0  try adding the column without the NOT NULL

User · Answer

I had a similar issue  You are trying to apply foreign key on a table which has content and the column is not nullable  You have two options    Make the column you want to apply foreign key constraints on to be nullable  That way the foreign key will apply knowing that some fields can be nullable   This is what i did   Create the column you want to apply foreign key constraint on  write a query to insert the foreign key into the column and then apply the foreign key constraints   Did not try this but it should work

User · Answer

Yet another weird case that gave me this error    I had erroneously referenced my foreign keys to the id primary key  This was caused by incorrect alter table commands   I found this out by querying the INFORMATION SCHEMA table  See this stackoverflow answer      The table was so confused it could not be fixed by any ALTER TABLE commands   I finally dropped the table and reconstructed it   This got rid of the integrityError

User · Answer

You re getting this error because you re trying to add update a row to table2 that does not have a valid value for the UserID field based on the values currently stored in table1  If you post some more code I can help you diagnose the specific cause

User · Answer

In case if the solutions provided by others didn t work  Then you should try checking the Database Engines of the Parent and Child Tables In my case  I had the parent tables  engine set to  MyISAM   changing it to InnoDB fixed it   Hope this helps others who are stuck like me

User · Answer

I had the same issue  and the reason was that I had a row in the first table before adding the foreign key

User · Answer

I just had the same problem the solution is easy   You are trying to add an id in the child table that does not exist in the parent table   check well  because InnoDB has the bug that sometimes increases the auto increment column without adding values  for example  INSERT     ON DUPLICATE KEY

User · Answer

Make sure the value that you are inserting into the foreign key exists in the parent table  That helped me  For example if you insert user id   2 into table 2  but table 1 does not have a user id   2  then the constraint will throw an error  Mine was error code  1452 to be exact  Hope this helps anyone else with the same problem

User · Answer

child table foreign key constraint is failing   This issue may rise due to following reason   If you are doing it in Spring mvc  you need to explicitly describe the id type  because sometimes mysql fails to recognize the type of id  so you explicitly set as in both tables in your entity class GeneratedValue  strategy   GenerationType IDENTITY

User · Answer

It means that you re trying to insert into table2 a UserID value that doesn t exist in table1

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

Examples related to mysql