CODEWITHSUNDEEP
sqlconstraintsforeign-keys
user198729
user198729

Reputation: 63626

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 problem?

Upvotes: 4

Views: 5040

Answers (5)

Kalpak
Kalpak

Reputation: 3530

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.

Upvotes: 0

Bartłomiej Zięba
Bartłomiej Zięba

Reputation: 31

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

Upvotes: 3

masoud ramezani
masoud ramezani

Reputation: 22920

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

Upvotes: 1

Paul Creasey
Paul Creasey

Reputation: 28824

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.

Upvotes: 4

Anton Gogolev
Anton Gogolev

Reputation: 115711

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.

Upvotes: 7

Related Questions