CODEWITHSUNDEEP
sqlforeign-keysconstraintssql-delete
Dave
Dave

Reputation: 1775

Deleting from table with Same Table foreign key constraint

I'm trying to run this sql server statement:

delete C from Company C, Company D where C.CompanyID > 1310 AND C.CompanyID != D.ParentID

I'm getting the following sql error:

The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Company_Company". The conflict occurred in database "DevelopmentDB", table "dbo.Company", column 'ParentID'.

I checked and there are no companies where ParentID = CompanyID. I'm curious why my delete statement isn't filtering out the companies that would cause this constraint to be broken.

Upvotes: 4

Views: 8639

Answers (2)

Jon Schneider
Jon Schneider

Reputation: 26973

I ran into this error ("The DELETE statement conflicted with the SAME TABLE REFERENCE constraint ...") in a situation where I had a table with columns and data like:

| RecordID | ParentRecordID | (other fields...) |
|----------|----------------|-------------------|
|        1 |           null |               ... |   
|        2 |              1 |               ... |

The error hit when my DELETE command tried to delete record 1 before record 2. The same-table foreign key constraint that had been set up on column ParentRecordID pointing at column RecordID prevented the delete: It wouldn't have been valid for record 2 to be pointing at record 1 when record 1 didn't exist anymore.

I worked around the problem by deleting records with a non-null ParentRecordID value first:

DELETE FROM myTable
WHERE ParentRecordID IS NOT null
  AND (other criteria...)

Then I could proceed to delete the remaining records with an additional DELETE statement:

DELETE FROM myTable
WHERE (other criteria...)

Upvotes: 3

RThomas
RThomas

Reputation: 10882

Have you verified the results of your attempt with something like this to ensure that what you are attempting to delete is what you actually intend to delete?

select C.<field list> from Company C, Company D 
where C.CompanyID > 1310 AND C.CompanyID != D.ParentID

You might also try the delete statement using a sub query approach. It might make it easier to logically identify that the query you are running is what you are actually intending.

However, I always recommend testing with a select first for verification.

So running (if this is your intended results - modify if not):

Select <field list> FROM Company WHERE CompanyID > 1310 
AND CompanyID NOT IN 
    (SELECT ParentID FROM Company)

Before running (again, this is just an example).:

DELETE Company WHERE CompanyID > 1310 
AND CompanyID NOT IN 
    (SELECT ParentID FROM Company)

Upvotes: 3

Related Questions