The DELETE statement conflicted with the REFERENCE constraint

Question

The DELETE statement conflicted with the REFERENCE constraint

Viewing 3 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

I looked back at your original post and realized that you are not deleting all of the records needed to perform the second delete. ALL records with the foreign key constraint have to be deleted in dbo.ProgramResult before any can be deleted from the next table. Since you are filtering which records are being deleted from dbo.ProgramResult on more than just the FK column, there are still records existing.

So, from a business standpoint... Either:

1. You are filtering too much on the delete from dbo.ProgramResult table.

OR

2. You don't really want to delete anything from the second table.

Jared
CE - Microsoft

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

sqlfriends (4/17/2012)
I tried to delete records in some tables.
I got an error:
The DELETE statement conflicted with the REFERENCE constraint :
The DELETE statement conflicted with the REFERENCE constraint "FK_ProgramResult_ProgramSnapshot". The conflict occurred in database "MyDB", table "dbo.ProgramResult".

Let's go back to the beginning for a moment. You've got FK_ProgramResult_ProgramSnapshot as your failing constraint. In your script below with the schema, this constraint isn't listed. It's on a table called ProgramResult. That table wants something you're deleting. If this is the exact error message you're getting, you haven't reviewed all the FKs involved in this table, it's being referenced by another table.

It's a references chain, you'll have to handle all the involved pieces.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

sqlfriend SSC Guru Points: 52794 More actions · Answer 3

Thanks, now I remove the where clause, it works.

The processID should take care of those.

And I learned too, in the future to post more about DDLs.

Thanks all.