How can a delete statement conflict with a REFERENCE constraint when there are no matching records in the referenced table?

Question

I run a stored procedure that deletes records, then re-populates them from a source system. The delete portion of the SP is structured according to the hierarchical layout of the tables, deleting from a referencing table (lets call it CHILD_TABLE) before deleting from referenced table (PARENT_TABLE). I am getting the "The DELETE statement conflicted with the REFERENCE constraint..." error when deleting from PARENT_TABLE.

There is a where clause on the delete statement and when I run a select statement with the same where clause I get 11 records returned. So we're trying to delete 11 records.

CHILD_TABLE references PARENT_TABLE with an FK column containing the PARENT_TABLE.PRIMARY_KEY. But when I run a select statement against CHILD_TABLE, using either of the following, I get 0 rows returned:

1. Copy and paste PARENT_TABLE.PRIMARY_KEY values from above select statement into where clause using IN
2. Copy and paste select statement above and put into where clause using IN against the FK/PK
3. Copy and paste select statement above and put into where clause using EXISTS WHERE etc.

So it looks to me like SQL Server thinks there is data in the CHILD_TABLE when there really isn't.

This question looks like a duplicate of "The DELETE statement conflicted with the REFERENCE constraint" while there is no data in referenced table but the answer there was (paraphrased) "There actually is data in the referenced table". However, in my case, there really is no data in the referenced table. No, really.

I was wondering if there is an index that is out of date that shows the reference constraint there is data when there really isn't?

Any help/pointers appreciated.

Answer 1

In my case, I found that the underlying issue was linked to the indexing on the table. When I dropped an index that referenced that same column that showed the FK constraint erroneously; the delete statement would work.

Therefore my proposed workaround would be:

• If you need the column to be indexed and have the FK constraint on it to the parent table, alter the existing FK constraint to ON DELETE CASCADE.

• If you do not need the index, then you may consider dropping it and the issue will be 'resolved'.

I have not been able to create a consistent replication scenario for this situation.