CODEWITHSUNDEEP
mysqlsqlforeign-keys
Mia Raunegger
Mia Raunegger

Reputation: 211

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

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?

Upvotes: 17

Views: 62647

Answers (4)

derick
derick

Reputation: 1

You have to set nulls to your from the parent to child tables Or set the same values eg Child table first nam=Derick Parent table name=Derick

Upvotes: 0

Cymbals
Cymbals

Reputation: 1174

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.

Upvotes: 7

Jakir Hosen Khan
Jakir Hosen Khan

Reputation: 1528

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)

Upvotes: 15

Mihai
Mihai

Reputation: 26784

Before you query run

SET FOREIGN_KEY_CHECKS=0

then set it to 1 after you`re done.

Upvotes: 37

Related Questions