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

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 proble开发者_如何学JAVAm?

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.

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.

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

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

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.