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

Question

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.

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

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.

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/

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!

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.