FIX: Referential integrity constraints are not evaluated correctly when query execution plan uses Foreign Key Reference Check operator in SQL Server 2016 and 2017

Applies to: SQL Server 2017 Developer WindowsSQL Server 2017 Enterprise WindowsSQL Server 2017 Enterprise Core Windows

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 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

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 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.

References


Learn about the terminology that Microsoft uses to describe software updates.