The reference constraint is being triggered but unable to select rows from the table that would trigger it

I am attempting to update a bunch of different tables in my database. However, when the update is completed, I am unable to delete a record from another table due to a foreign key constraint. However, other databases on different servers will accept the change just fine. And upon creating a fresh new db and applying a series of update scripts against it to pre-populate the data, it fails as well.

It should be SQL Server 2016.

I have had a coworker attempt to rebuild indexes and update usages and it still failed.

--- Query 1
SELECT  ROW_NUMBER() OVER(ORDER BY c.[name], t.[name]),
        SCHEMA_NAME(t.schema_id) AS SchemaName,
        c.[name] AS ColName, 
        t.[name] AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE   c.[name] IN (...)
AND     t.[name] NOT IN (...)
ORDER BY ColName, TableName

I select out a collection of schema, columns and tables that meet my criteria and generate dynamic SQL for each combination.

--- Query 2
SET @sql = 'UPDATE ' + @schemaName + '.' + @tableName + ' SET ' + @colName + ' = ' + CONVERT(NVARCHAR, @p1) + ' WHERE ' + @colName + ' = ' + CONVERT(NVARCHAR, @p2)

Most/all of the tables returned by query 1 have a FK constraint against another table. And query 2 seems to execute correctly without issue.

Table definition is as follows:

CREATE TABLE [Table1](
    [Table1ID] [int] NOT NULL,
    [ColX] [int] NOT NULL,
    [ColY] [int] NOT NULL,
    ...
    [ColZ] [int] NOT NULL,
    ...
) ON [PRIMARY]

ALTER TABLE [Table1] ADD  CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED 
(
    [Table1ID] ASC,
    [ColY] ASC,
    [ColZ] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

ALTER TABLE [Table1]  WITH CHECK ADD  CONSTRAINT [FK_Table1_ColY_Table2_ColY] FOREIGN KEY([ColY])
REFERENCES [Table2] ([ColY])

CREATE TABLE [Table2](
    [Table2ID] [int] IDENTITY(1,1) NOT NULL,
    [ColY] [int] NOT NULL,
    [ColZ] [int] NOT NULL,
    ...
) ON [PRIMARY]

ALTER TABLE [Table2] ADD  CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED 
(
    [Table2ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

ALTER TABLE [Table2] ADD  CONSTRAINT [UK_Table2_ColY] UNIQUE NONCLUSTERED 
(
    [ColY] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

My expectation is that when I run the following:

DELETE  FROM Table2
WHERE   ColY = @p2

that I do not trigger FK_Table1_ColY_Table2_ColY.

Especially because:

SELECT   *
FROM     Table1
WHERE    ColY = @p2

returns no records.

The complete error message is as follows:

Msg 547, Level 16, State 0
The DELETE statement conflicted with the REFERENCE constraint 
"FK_Table1_ColY_Table2_ColY". The conflict occurred in database "localhost", table 
"dbo.Table1", column 'ColY'.

Answer

It seems that there was an issue fixed in an update to sql server.

Support Microsoft: Fix referential integrity constraints not being evaluated correctly.

The work around fixes detailed are:

1. Use a compatibility level below 130.

2. Modify the referencing table to change the index structure.