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

 Answers

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.

---

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.

---

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.

---

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

---

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)

---

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

---

İf 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)