tsql - ADO.net SQL Server 2012 - 使用复合键和 MissingSchemaAction.AddWithKey 查询错误地添加了约束

Question

这个查询，当在 ADO.net 上运行时 MissingSchemaAction.AddWithKey抛出异常:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

询问:

SELECT map.GroupId, b.PersonId 
FROM [GroupPersonMap] as map
INNER JOIN [Person] AS b ON b.PersonId = map.PersonId
GROUP BY map.GroupId, b.PersonId 

检查局部变量显示 PersonId 的唯一约束已添加。不仅如此，在 SQL Server 管理器中运行相同的查询会返回一个没有任何警告或错误的结果集。此确切代码用于在 SQL Server 2005 上工作。使用 SQL Server 2005，在 ADO.net 上运行此查询时，该查询会正确创建复合约束。这是升级问题吗？

作为旁注，我知道设置 EnforceConstraints = false提供了一种解决方法。理想情况下，我想从根本上解决这个问题。

设置重现:

CREATE TABLE [GroupPersonMap]
(
[GroupId] [int] NOT NULL,
[PersonId] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [GroupPersonMap] ADD CONSTRAINT [PK_GroupPersonMAP] PRIMARY KEY CLUSTERED  ([GroupId], [PersonId])

CREATE TABLE [Person]
(
[PersonId] [int] NOT NULL IDENTITY(1, 1),
[Val] INT
) ON [PRIMARY]
GO
ALTER TABLE [Person] ADD CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED  ([PersonId])

然后插入值:

INSERT INTO [GroupPersonMap] 
SELECT 1, 1
UNION ALL
SELECT 2, 1

INSERT INTO [Person]
SELECT 1

Answer 1

您可能会在这篇文章中找到一些有用的调试建议:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

也就是说，在这里让我跳出来的第一件事是你所有的加入和分组都是不必要的。你写

SELECT map.GroupId, b.PersonId 
FROM [GroupPersonMap] as map
INNER JOIN [Person] AS b ON b.PersonId = map.PersonId
GROUP BY map.GroupId, b.PersonId 

但你可以得到相同的结果

SELECT * FROM GroupPersonMap

这就是我的意思:

1) 除了 b.PersonId，您没有从表 b (Person) 中选择任何字段。但是您从 JOIN 子句中知道 b.PersonId 的值必须等于 map.PersonId，因此没有来自 Person 的信息不在 GroupPersonMap 中。所以，我们可以去掉 JOIN:

SELECT map.GroupId, map.PersonId 
FROM [GroupPersonMap] as map
GROUP BY map.GroupId, map.PersonId 

2) 但是现在， map.GroupId, map.PersonId是该表的确切主键，因此我们知道不会发生实际聚合——根据定义，GroupId/PersonId 的每个组合都是唯一的。因此，每一行输入都将“分组到”一行且只有一行输出。这意味着我们也可以去掉 GROUP BY 子句:

SELECT map.GroupId, map.PersonId 
FROM [GroupPersonMap] as map

3) 现在，我们在 SELECT 子句中剩下的是 map.GroupId, map.PersonId -- 这就是表中的所有字段。因此，您可以进一步简化为“SELECT * FROM GroupPersonMap”，但实际上对于生产代码，最好始终枚举您想要的字段。 (准确指定您想要的内容可以保护您免受以后的架构更改。)

关于 JOIN 的另一个注意事项:当您通过主键连接表时，理想情况下您希望连接整个键。 (这是著名的“第三范式”的“一阶”。)当表具有复合键时，这意味着键中的每个字段都包含 JOIN 和/或 WHERE 条件。否则，您可能会使用半笛卡尔连接，或者在这种情况下，可能会出现主键的另一半未指定的问题。我想，如果您想保持查询原样但仍然消除您的错误，您可以简单地说出您对哪个组感兴趣(例如 WHERE map.GroupId = xxxx)。如果您确实想要列出所有内容，那么我会说没有不必要的 JOINing 和 GROUPing 的简化重写将是您最好的第一线攻击。