时间戳

首页 > 解决方案 > SQL 唯一约束允许违反空值

sql - SQL 唯一约束允许违反空值

问题描述

我有一个表，它在 3 列“MenuId”、“Name”和“ParentId”上有一个非聚集唯一索引。

问题是，我可以在该表中插入违反唯一约束的多行 - 当 ParentId 设置为 NULL 时，如下所示。

如果我尝试添加重复行并且 ParentId 不为空，则唯一约束将按预期工作。

我对唯一索引的理解是它只允许参与索引的列的单个唯一组合，所以我希望在下图中能够插入第一行，但它应该抛出一个违反的异常插入第二（和第三）行时的索引 - 但事实并非如此。

我哪里错了？

我正在使用 SQL LocalDb。

LocalDb 中的表

更新：我正在使用 EF Core 从代码生成数据库，似乎在这种情况下，创建唯一约束时使用 WHERE 子句在 ParentId 或 Name 为 NULL 时忽略：

CREATE UNIQUE NONCLUSTERED INDEX [IX_MenuItem_MenuId_ParentId_Name]
    ON [dbo].[MenuItem]([MenuId] ASC, [ParentId] ASC, [Name] ASC) 
WHERE ([ParentId] IS NOT NULL AND [Name] IS NOT NULL);

令人尴尬的是，当我在 SQL Server Explorer 属性窗口中探索 Unique 约束时，这个 WHERE 子句并不明显——我只是检查了 Unique 约束是否存在，并且我期望的列包含在其中——但这还不够。感谢下面的评论者，他无法复制并让我实际检查脚本以查找此 WHERE 子句变得明显的索引。我现在已将我的问题转移到与 EF 为什么在我的情况下产生此问题有关的问题： https ://github.com/aspnet/EntityFrameworkCore/issues/17586

标签： sqlsql-server-expresslocaldb

解决方案

感谢@萧为元提示我更详细地查看 Unique 约束，而不仅仅是在属性窗口中！

UNIQUE 索引有一个 WHERE 子句，导致它忽略父 ID 或名称为 NULL 值的记录：

CREATE UNIQUE NONCLUSTERED INDEX [IX_MenuItem_MenuId_ParentId_Name]
    ON [dbo].[MenuItem]([MenuId] ASC, [ParentId] ASC, [Name] ASC) 
WHERE ([ParentId] IS NOT NULL AND [Name] IS NOT NULL);

这是由 EF 生成的——这就是你在依赖 ORM 为你做事并假设他们做你想做的事情时遇到的问题类型——给自己一巴掌

推荐阅读