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

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.

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

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/

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.

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.

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!

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 :)

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.

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

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...

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)

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

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

You should not put an ondelete field against a cascade in the database.

So set the onDelete field to RESTRICT

Good luck ?

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<Table2> table2List;

In Table2 Entity:

@Column(insertable = false, nullable = true)
private int UserID;

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.

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.

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.

1. 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.)
2. 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)

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.

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..

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!

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

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

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     [email protected]

If you try to ALTER table2 and add a foreign key then the query will fail because UserID=5 doesn't exist in Table1.

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.