Assume that you try to delete data from a table, and the data is being referenced in other tables in Microsoft SQL Server 2016 and 2017. If you explore queryexecution plan, you can see it use a Foreign Key Reference Check operator. In this situation, you may receive a following error message even if the row beingdeleted is not referenced in other tables:
Msg 547, Level 16, State 0, Line LineNumber
The DELETE statement conflicted with the REFERENCE constraint
This issue is fixed in the following cumulative update for SQL Server:
Note This issue is fixed in the Cumulative Update 6 for SQL Server 2016 SP2.
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
To work around this issue, use one of the following methods:
- Use compatibility level below 130 to run this query.
Change index structure on the referencing table.
Starting database scoped configuration compatibility level130 in SQL Server 2016, a query execution plan can use Foreign Key ReferenceCheck operator to verify if a row in a table is referenced by any rows in othertables through referential integrity constraints. Such operator can be used ifa table is referenced by a large number of foreign tables. In this operator,SQL Server chooses an index in each referencing table to perform such check. Ifa referencing table has an index with multiple key columns, such somecolumns at the beginning are not part of the foreign key, but some othercolumns are part of the foreign key. This may result in incorrect check that's performedby SQL Server.
For example, consider the following schema:
create table tpk (pk int constraintcpk primary key clustered)
create table tfk (a int, fk intconstraint cfk foreign key references tpk, index ia clustered(a,fk))
Such schema may result in this issue, but if you changeorder of columns in index ia to (fk,a), it will not.