具有重复 NULL 的 SQL Server UNIQUE 约束 [英] SQL Server UNIQUE constraint with duplicate NULLs

问题描述

可能的重复:
如何创建唯一约束这也允许 sql server 中的空值

我有一个表，我需要在其中强制列具有唯一值.此列必须可以为空，并且根据业务逻辑，应允许多个 NULL 值，而其他重复值则不允许.

I have a table where I need to force a column to have unique values. This column must be nullable and by business logic multiple NULL values should be permitted, whereas other duplicate values are not.

SQL Server UNIQUE 约束在这种情况下不好用，因为它将 NULL 视为常规值，因此它会拒绝重复的 NULL.

SQL Server UNIQUE constraint is no good in this situation because it considers NULL as regular values, so it will reject duplicate NULLs.

目前，价值唯一性是由 BLL 授予的，所以我不是在寻找一个肮脏的黑客来使它工作.我只想知道是否有一个干净的解决方案来在数据库中强制执行此约束.

Currently, value uniqueness is granted by the BLL so I'm not looking for a dirty hack to make it work. I just would like to know if there is a clean solution to enforce this constraint in the DB.

是的，我知道我可以编写一个触发器来做到这一点:触发器是唯一的解决方案吗?(还是最好的解决方案?)

And yeah, I know I can write a trigger to do that: is a trigger the only solution? (or the best solution anyway?)

推荐答案

如果您使用的是 SQL Server 2008(不适用于早期版本)，则存在过滤索引的概念.您可以在表的过滤子集上创建索引.

If you're using SQL Server 2008 (won't work for earlier version) there is the concept of a filtered index. You can create the index on a filtered subset of the table.

CREATE UNIQUE INDEX indexName ON tableName(columns) INCLUDE includeColumns 
WHERE columnName IS NOT NULL

这篇关于具有重复 NULL 的 SQL Server UNIQUE 约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！