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

Question

Delete any entry from them, report the error as follows:

ERROR 1451 (23000): Cannot delete or update parent row: foreign key constraint does not work

How to solve this problem?

Answer 1

Select a record that allows you to insert null in the FK column, insert there, delete another record, and then delete the first.

Answer 2

or

 ON DELETE CASCADE 

or insert NULL for one of the FK, if possible in your scheme, cannot provide more information without knowing the seller.

Answer 3

if your column is AllowNull, update its data to zero, and then delete the data.

Answer 4

If both tables reference a foreign key to each other, it is very likely that the database design needs to be improved. I think 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 zero, and then start the deletion in both tables.

But in order for this to happen, make sure that you have identified PK in advance to remove them.

Kalpak

Answer 5

 ALTER TABLE <table1> NOCHECK CONSTRAINT ALL GO ALTER TABLE <table2> NOCHECK CONSTRAINT ALL GO DELETE FROM <table1> GO DELETE FROM <table2> GO ALTER TABLE <table1> CHECK CONSTRAINT ALL GO ALTER TABLE <table2> CHECK CONSTRAINT ALL GO