SQL 如何在 NULL 列上创建唯一索引？

How to create a unique index on a NULL column?

提问by Nuno G

I am using SQL Server 2005. I want to constrain the values in a column to be unique, while allowing NULLS.

我使用的是 SQL Server 2005。我想将列中的值限制为唯一，同时允许 NULLS。

My current solution involves a unique index on a view like so:

我当前的解决方案涉及视图上的唯一索引，如下所示：

CREATE VIEW vw_unq WITH SCHEMABINDING AS
    SELECT Column1
      FROM MyTable
     WHERE Column1 IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)

Any better ideas?

有什么更好的想法吗？

采纳答案by willasaywhat

Pretty sure you can't do that, as it violates the purpose of uniques.

很确定你不能这样做，因为它违反了唯一性的目的。

However, this person seems to have a decent work around: http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

然而，这个人似乎有一个体面的工作：http: //sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

回答by Phil Haselden

Using SQL Server 2008, you can create a filtered index: http://msdn.microsoft.com/en-us/library/cc280372.aspx. (I see Simon added this as a comment, but thought it deserved its own answer as the comment is easily missed.)

使用 SQL Server 2008，您可以创建筛选索引：http: //msdn.microsoft.com/en-us/library/cc280372.aspx。（我看到 Simon 将此添加为评论，但认为它应该得到自己的答案，因为该评论很容易被错过。）

Another option is a trigger to check uniqueness, but this could affect performance.

另一种选择是检查唯一性的触发器，但这可能会影响性能。

回答by onedaywhen

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

计算列技巧被广泛称为“nullbuster”；我的笔记归功于史蒂夫·卡斯：

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

回答by roy

Strictly speaking, a unique nullable column (or set of columns) can be NULL (or a record of NULLs) only once, since having the same value (and this includes NULL) more than once obviously violates the unique constraint.

严格来说，唯一的可空列（或一组列）只能是一次 NULL（或 NULL 记录），因为多次具有相同的值（包括 NULL）显然违反了唯一约束。

However, that doesn't mean the concept of "unique nullable columns" is valid; to actually implement it in any relational database we just have to bear in mind that this kind of databases are meant to be normalized to properly work, and normalization usually involves the addition of several (non-entity) extra tables to establish relationships between the entities.

然而，这并不意味着“唯一可空列”的概念是有效的；要在任何关系数据库中实际实现它，我们只需要记住，这种数据库旨在规范化以正常工作，规范化通常涉及添加几个（非实体）额外表来建立实体之间的关系.

Let's work a basic example considering only one "unique nullable column", it's easy to expand it to more such columns.

让我们做一个仅考虑一个“唯一可为空列”的基本示例，很容易将其扩展到更多这样的列。

Suppose we the information represented by a table like this:

假设我们用这样的表格表示的信息：

create table the_entity_incorrect
(
  id integer,
  uniqnull integer null, /* we want this to be "unique and nullable" */
  primary key (id)
);

We can do it by putting uniqnull apart and adding a second table to establish a relationship between uniqnull values and the_entity (rather than having uniqnull "inside" the_entity):

我们可以通过将 uniqnull 分开并添加第二个表来建立 uniqnull 值和 the_entity 之间的关系（而不是在 the_entity“内部”使用 uniqnull）来实现：

create table the_entity
(
  id integer,
  primary key(id)
);

create table the_relation
(
  the_entity_id integer not null,
  uniqnull integer not null,

  unique(the_entity_id),
  unique(uniqnull),
  /* primary key can be both or either of the_entity_id or uniqnull */
  primary key (the_entity_id, uniqnull), 
  foreign key (the_entity_id) references the_entity(id)
);

To associate a value of uniqnull to a row in the_entity we need to also add a row in the_relation.

要将 uniqnull 的值与 the_entity 中的一行相关联，我们还需要在 the_relation 中添加一行。

For rows in the_entity were no uniqnull values are associated (i.e. for the ones we would put NULL in the_entity_incorrect) we simply do not add a row in the_relation.

对于 the_entity 中的行没有关联 uniqnull 值（即对于那些我们将 NULL 放入 the_entity_incorrect 的行），我们只是不在 the_relation 中添加行。

Note that values for uniqnull will be unique for all the_relation, and also notice that for each value in the_entity there can be at most one value in the_relation, since the primary and foreign keys on it enforce this.

请注意，uniqnull 的值对于所有 the_relation 都是唯一的，并且还要注意，对于 the_entity 中的每个值，the_relation 中最多只能有一个值，因为它的主键和外键强制执行此操作。

Then, if a value of 5 for uniqnull is to be associated with an the_entity id of 3, we need to:

然后，如果 uniqnull 的值 5 与 the_entity id 3 相关联，我们需要：

start transaction;
insert into the_entity (id) values (3); 
insert into the_relation (the_entity_id, uniqnull) values (3, 5);
commit;

And, if an id value of 10 for the_entity has no uniqnull counterpart, we only do:

并且，如果 the_entity 的 id 值为 10 没有 uniqnull 对应项，我们只做：

start transaction;
insert into the_entity (id) values (10); 
commit;

To denormalize this information and obtain the data a table like the_entity_incorrect would hold, we need to:

要对这些信息进行反规范化并获得像 the_entity_incorrect 这样的表将保存的数据，我们需要：

select
  id, uniqnull
from
  the_entity left outer join the_relation
on
  the_entity.id = the_relation.the_entity_id
;

The "left outer join" operator ensures all rows from the_entity will appear in the result, putting NULL in the uniqnull column when no matching columns are present in the_relation.

“左外连接”运算符确保来自 the_entity 的所有行都将出现在结果中，当 the_relation 中不存在匹配的列时，将 NULL 放在 uniqnull 列中。

Remember, any effort spent for some days (or weeks or months) in designing a well normalized database (and the corresponding denormalizing views and procedures) will save you years (or decades) of pain and wasted resources.

请记住，在设计良好规范化的数据库（以及相应的非规范化视图和程序）方面花费数天（或数周或数月）的任何努力都将为您节省数年（或数十年）的痛苦和浪费的资源。