How to delete a record when two tables have foreign key referenced to each other?

Question

Delete any record of them will report an error like this:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

How to overcome this problem?

Answer 1

Pick a record which allows nulls to be inserted in a FK column, insert it there, delete other record and then delete the first one.

Answer 2

either

ON DELETE CASCADE

or insert NULL for one of the FK's if thats possible in your schema, can't give more info without knowing the vendor.

Answer 3

Hi,

If both the tables are referencing foreign key on each other, it is highly likely that the database design needs improvement. I guess this should call an extra table to take care of this design.

For your question, you can update the FK key field in one table to be null and then fire a delete on both tables.

But to make this happen, make sure you identify both the PK in advance for them to be deleted.

Kalpak

Answer 4

if your column is AllowNull, update its data to null then delete data.