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 null
s 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.
精彩评论