如何在空列上创建唯一索引?

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

我正在使用SQL Server 2005。我希望将列中的值约束为惟一，同时允许使用null。

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?

有更好的主意吗?

4 个解决方案

#1

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

很肯定你做不到，因为它违背了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/multinull -values-in unique—index .html

#2

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 it's own answer as the comment is easily missed)

使用SQL Server 2008，您可以创建一个经过过滤的索引:http://msdn.microsoft.com/en-us/library/cc280372.aspx。(我看到西蒙加了这句话作为评论，但我认为它应该得到自己的回答，因为很容易漏掉这句话)

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

另一个选项是检查惟一性的触发器，但这可能会影响性能。

#3

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

#4

-1

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)显然违反了唯一的约束。

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的值与_entity中的一行关联起来，我们还需要在_关系中添加一行。

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值关联(例如，对于那些在the_entity_wrong中输入NULL的行)，我们只需在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的值对于所有的关系都是唯一的，并且注意到在_entity中每个值最多只能有一个值，因为它的主键和外键执行这个。

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

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

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:

并且，如果id值为10的the_entity没有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_wrong这样的表将保存的数据，我们需要:

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_relations中不存在匹配列时，将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.

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

#1