title: How can a delete statement conflict with a REFERENCE constraint when there are no matching records in the referenced table?
body: <p>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 <code>CHILD_TABLE</code>) before deleting from referenced table (<code>PARENT_TABLE</code>). I am getting the "The DELETE statement conflicted with the REFERENCE constraint..." error when deleting from <code>PARENT_TABLE</code>.</p>
  
  <p>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.</p>
  
  <p><code>CHILD_TABLE</code> references <code>PARENT_TABLE</code> with an FK column containing the <code>PARENT_TABLE.PRIMARY_KEY</code>. But when I run a select statement against <code>CHILD_TABLE</code>, using either of the following, I get 0 rows returned:</p>
  
  <ol>
  <li>Copy and paste <code>PARENT_TABLE.PRIMARY_KEY</code> values from above select statement into where clause using <code>IN</code></li>
  <li>Copy and paste select statement above and put into where clause using <code>IN</code> against the FK/PK</li>
  <li>Copy and paste select statement above and put into where clause using <code>EXISTS WHERE</code> etc.</li>
  </ol>
  
  <p>So it looks to me like SQL Server thinks there is data in the <code>CHILD_TABLE</code> when there really isn't.</p>
  
  <p>This question looks like a duplicate of <a href="https://stackoverflow.com/questions/29890885/the-delete-statement-conflicted-with-the-reference-constraint-while-there-is-n">&quot;The DELETE statement conflicted with the REFERENCE constraint&quot; while there is no data in referenced table</a> 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.</p>
  
  <p>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?</p>
  
  <p>Any help/pointers appreciated.</p>