Symptoms
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 query execution 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 being deleted is not referenced in other tables:
Msg 547, Level 16, State 0, Line LineNumber
The DELETE statement conflicted with the REFERENCE constraint
Status
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Resolution
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:
Workaround
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.
More Information
Starting database scoped configuration compatibility level 130 in SQL Server 2016, a query execution plan can use Foreign Key Reference Check operator to verify if a row in a table is referenced by any rows in other tables through referential integrity constraints. Such operator can be used if a 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. If a referencing table has an index with multiple key columns, such some columns at the beginning are not part of the foreign key, but some other columns are part of the foreign key. This may result in incorrect check that's performed by SQL Server.
For example, consider the following schema:
create table tpk (pk int constraint cpk primary key clustered)
create table tfk (a int, fk int constraint cfk foreign key references tpk, index ia clustered(a,fk))
Such schema may result in this issue, but if you change order of columns in index ia to (fk,a), it will not.
References
Learn about the terminology that Microsoft uses to describe software updates.
