具有重复空值的SQL Server唯一约束[duplicate]

Possible Duplicate:
How do I create unique constraint that also allows nulls in sql server

可能的重复:如何创建惟一约束，该约束也允许sql server中的nulls

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 constraint is no good in this situation because it considers NULL as regular values, so it will reject duplicate NULLs.

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

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.

目前，值惟一性是由BLL授予的，所以我不是在寻找一个肮脏的hack来让它工作。我只是想知道是否有一个干净的解决方案可以在DB中执行这个约束。

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

是的，我知道我可以写一个触发器来实现这个:触发器是唯一的解决方案吗?(或者说是最好的解决方案?)

6 个解决方案

#1

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.

如果您使用的是SQL Server 2008(不适合早期版本)，那么就有了过滤索引的概念。您可以在经过筛选的表子集上创建索引。

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

#2

Duplicate of this question?

重复这个问题吗?

The calculated column trick is widely known as a "nullbuster"; my notes credit Steve Kass:

计算列技巧被广泛称为“nullbuster”;我的纸币，Steve Kass:

CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X  int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)

Works on SQL Server 2000. You may need ARITHABORT on e.g.

在SQL Server 2000上工作。你可能需要算术。

ALTER DATABASE MyDatabase SET ARITHABORT ON

#3

If you're using SQL Server 2008, have a look into Filtered Indexes to achieve what you want.

如果您正在使用SQL Server 2008，请查看过滤后的索引，以实现您想要的。

For older version of SQL Server, a possible alternative to a trigger involves a computed column:

对于较老版本的SQL Server，一个可能的触发器替代方案涉及一个计算列:

Create a computed column which uses the value of your "unique" column if it's not NULL, otherwise it uses the value of the row's Primary Key column (or any column which will be unique).
创建一个计算列，如果该列不是NULL，则使用“惟一”列的值，否则将使用该行主键列的值(或任何惟一的列)。
Apply a UNIQUE constraint to the computed column.
对计算列应用唯一的约束。

#4

http://www.sqlmag.com/article/articleid/98678/sql_server_blog_98678.html

will work only in Microsoft SQL Server 2008

#5

You can create a view in which you select only not null values and create an index on it.

您可以创建一个视图，在其中只选择not null值并在其上创建索引。

Here is the source - Creating Indexed Views

这是源创建索引视图

#6

You should use UNIQUEIDENTIFIER in that column, can be NULL and also is unique by definition. Hope that helps.

您应该在该列中使用UNIQUEIDENTIFIER，它可以是NULL，并且根据定义也是唯一的。希望有帮助。

#1