参考约束是触发的，但无法从表触发的表中选择行

Question

我正在尝试更新数据库中的一堆不同表。但是，当更新完成后，由于外键约束，我无法从另一个表中删除记录。但是，不同服务器上的其他数据库将可以接受更改。并在创建一个新的新数据库并应用一系列更新脚本以预先填充数据后，它也会失败。

应该是SQL Server2016。

我有一个同事尝试重建索引和更新用法，但它仍然失败。

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

我选择了符合我标准并为每种组合生成动态SQL的架构，列和表的集合。

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

查询1返回的大多数/所有表都具有另一个表的FK约束。查询2似乎没有问题就可以正确执行。

表定义如下：

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]

我的期望是当我运行以下内容时：

DELETE  FROM Table2
WHERE   ColY = @p2

我不触发fk_table1_coly_table2_coly。

特别是因为：

SELECT   *
FROM     Table1
WHERE    ColY = @p2

返回没有记录。

完整的错误消息如下：

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 1

似乎在更新到SQL Server中解决了问题。

支持Microsoft：修复未正确评估的参考完整性约束。

详细修复的工作是：

1. 使用以下130的兼容性级别。

2. 修改引用表以更改索引结构。