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

Question

I'm getting this error:

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

I've located them and try to get rid of the references with

alter table tillhör drop foreign key kat_id;

But getting this error instead:

#1025 - Error on rename of '.\recept\tillh@1ir' to '.\recept#sql2-1570-3cb' (errno: 152).

What do I do wrong?

Answer 1

Before you query run

SET FOREIGN_KEY_CHECKS=0

then set it to 1 after you`re done.

Answer 2

I face same problem. I solve this issue by clearing, i.e. deleting all data from child table and successfully done.

This is occur if child table contain some data with the foreign key that that are not in parent table i.e, if there are two table called Person (with column id, name, address) and order(with column id, person_id, order_name); order.person_id is foreign key of person.id and order table contain person_id that is not present in person table.

You can solve this using the following query

Delete from order where person_id NOT IN (select id from person where person.id = order.person_id)

Answer 3

When I had this problem, it was due to the fact that I forgot to specify NULLS allowed when creating the foreign key id fields. I changed it after the fact, but 0's were already in the value. It could not find 0 in the matching table, and then gave this error. The fix: update the zero values to nulls.