sql-server - SQL Server中如何允许在带有唯一约束的字段中输入大量空值

Question

create table test3
(
id int PRIMARY KEY,
id2 int
);

create unique index ndx_id2 on test3 (id2);

出于唯一索引的目的，所有 NULL 值都被认为不同于所有其他 NULL 值，因此是唯一的。这是 SQL-92 标准的两种可能解释之一(标准中的语言是模棱两可的)，也是 PostgreSQL、MySQL、Firebird 和 Oracle 遵循的解释。

Informix 和 Microsoft SQL Server 遵循标准的其他解释。

INSERT INTO test3(id, id2) VALUES (1, null);
INSERT INTO test3(id, id2) VALUES (2, null);

第二个 INSERT 返回

A duplicate value cannot be inserted into a unique index. [ Table name = test3,Constraint name = ndx_id2 ]

SQL Server 出错，但成功将记录添加到另一个 DBMS，例如 Sqlite。

SQL Server中如何允许在带有唯一约束的字段中输入大量空值？

Answer 1

从 SQL Server 2008 开始，您可以使用 filtered index :

create unique index UX_YourIndex
on YourTable(col1) 
where col1 is not null

Example at SQL Fiddle.

对于 SQL Server 2005 及更早版本 (9.0 corresponds to 2005)，您可以使用代理项列。代理项列是计算列。计算如下:

• 当半唯一列不为null时，代理列等于该列
• 当半唯一列为null 时，代理列等于唯一标识。唯一 ID 可以是标识列、rowguid 或每行不同的任何内容。

换句话说，只要原始列为 null，代理列就会使用唯一的字符串。例如:

create table YourTable 
    (
    id int identity primary key
,   col1 int
,   surrogate1 as (coalesce(convert(varchar(20), col1), '_' + convert(varchar(20), id)))
    );

create unique index UX_YourIndex on YourTable(surrogate1);

您的工作是确保替代值不会与实际值发生冲突。在此示例中，我假设 col1 不能以下划线开头。

Example at SQL Fiddle.