字段值必须是唯一的，除非它为NULL

I'm using SQL Server 2005.

我正在使用SQL Server 2005。

I have a field that must either contain a unique value or a NULL value. I think I should be enforcing this with either a CHECK CONSTRAINT or a TRIGGER for INSERT, UPDATE.

我有一个必须包含唯一值或NULL值的字段。我认为我应该使用CHECK CONSTRAINT或TRIGGER来执行此操作，用于INSERT，UPDATE。

Is there an advantage to using a constraint here over a trigger (or vice-versa)? What might such a constraint/trigger look like?

在触发器上使用约束是否有优势（反之亦然）？这样的约束/触发器可能是什么样的？

Or is there another, more appropriate option that I haven't considered?

或者是否有其他更合适的选择我没有考虑过？

7 个解决方案

#1

Here is an alternative way to do it with a constraint. In order to enforce this constraint you'll need a function that counts the number of occurrences of the field value. In your constraint, simply make sure this maximum is 1.

以下是使用约束执行此操作的另一种方法。为了强制执行此约束，您需要一个计算字段值出现次数的函数。在约束中，只需确保此最大值为1。

Constraint:

约束：

   field is null or dbo.fn_count_maximum_of_field(field) < 2

EDIT I can't remember right now -- and can't check it either -- whether the constraint check is done before the insert/update or after. I think after with the insert/update being rolled back on failure. If it turns out I'm wrong, the 2 above should be a 1.

编辑我现在不记得 - 也无法检查 - 是否在插入/更新之前或之后完成约束检查。我认为插入/更新在失败后回滚。如果事实证明我错了，上面的2应该是1。

Table function returns an int and uses the following select to derive it

Table函数返回一个int并使用以下select来派生它

   declare @retVal int

   select @retVal = max(occurrences)
   from ( 
        select field, count(*) as occurrences
        from dbo.tbl
        where field = @field
        group by field
   ) tmp

This should be reasonably fast if your column as a (non-unique) index on it.

如果您的列作为（非唯一）索引，这应该相当快。

#2

I create a view with the an index that ignores the nulls through the where clause...i.e. if you insert null into the table the view doesn't care but if you insert a non null value the view will enforce the constraint.

我创建了一个带有索引的视图，该索引通过where子句忽略空值...即。如果在表中插入null，则视图不关心，但如果插入非null值，则视图将强制执行约束。

create view dbo.UniqueAssetTag with schemabinding
as
select asset_tag
from dbo.equipment
where asset_tag is not null

GO

create unique clustered index ix_UniqueAssetTag
on UniqueAssetTag(asset_tag)

GO

So now my equipment table has an asset_tag column that allows multiple nulls but only unique non null values.

所以现在我的设备表有一个asset_tag列，它允许多个空值，但只允许唯一的非空值。

Note: If using mssql 2000, you'll need to "SET ARITHABORT ON" right before any insert, update or delete is performed on the table. Pretty sure this is not required on mssql 2005 and up.

注意：如果使用mssql 2000，则需要在对表执行任何插入，更新或删除之前“SET ARITHABORT ON”。很确定mssql 2005及以上版本不需要这样做。

#3

You can accomplish this by creating a computed column and put the unique index on that column.

您可以通过创建计算列并将唯一索引放在该列上来实现此目的。

ALTER TABLE MYTABLE 
ADD COL2 AS (CASE WHEN COL1 IS NULL THEN CAST(ID AS NVARCHAR(255)) ELSE COL1 END)

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2)

This is assuming that ID is the PK of your table and COL1 is the "unique or null" column.

这假设ID是表的PK，COL1是“唯一或空”列。

The computed column (COL2) will use the PK's value if your "unique" column is null.

如果“唯一”列为空，则计算列（COL2）将使用PK的值。

There is still the possibility of collisions between the ID column and COL1 in the following example:

在以下示例中，ID列和COL1之间仍可能发生冲突：

ID     COL1    COL2
1     [NULL]    1
2        1      1

To get around this I usually create another computed column which stores whether the value in COL2 comes from the ID column or the COL1 column:

为了解决这个问题，我通常会创建另一个计算列，用于存储COL2中的值是来自ID列还是COL1列：

 ALTER TABLE MYTABLE 
 ADD COL3 AS (CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END)

The index should be changed to:

索引应更改为：

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2, COL3)

Now the index is on both computed columns COL2 and COL3 so there is no issue:

现在索引在计算列COL2和COL3上，所以没有问题：

ID     COL1    COL2   COL3
1     [NULL]    1       1
2        1      1       0

#4

In Oracle, a unique key will permit multiple NULLs.

在Oracle中，唯一键将允许多个NULL。

In SQL Server 2005, a good approach is to do your inserts through a view, and disable direct inserts into the table.

在SQL Server 2005中，一种好的方法是通过视图进行插入，并禁用直接插入表中。

Here is some sample code.

这是一些示例代码。

#5

Is there a primary key on this table, maybe an Identity column? You could create a unique key that is a composite of the field you are enforcing uniqueness on in combination with the primary key.

这个表上是否有主键，可能是Identity列？您可以创建一个唯一键，该键是与主键一起强制执行唯一性的字段的组合。

There is a discussion about just this kind of issue here: http://blog.sqlauthority.com/2008/09/07/sql-server-explanation-about-usage-of-unique-index-and-unique-constraint/

这里有一个关于这类问题的讨论：http：//blog.sqlauthority.com/2008/09/07/sql-server-explanation-about-usage-of-unique-index-and-unique-constraint/

FYI - SQL Server 2008 introduces filtered indexes which would allow you to approach this a bit differently.

仅供参考 - SQL Server 2008引入了过滤索引，允许您以不同方式处理此问题。

#6

Usually a trigger will allow you to provide a more verbose and explanatory message than a check constraint, so I have used those to avoid the "which column was bad" game in debugging.

通常，触发器将允许您提供比检查约束更详细和解释性的消息，因此我使用这些消息来避免调试中的“哪一列是坏的”游戏。

#7

A constraint is far lighter than a trigger, even though a unique constraint is effectively an index.

约束比触发器轻得多，即使唯一约束实际上是索引。

However, you are only allowed one NULL in a unique constraint/index. So, you'll have to use a trigger to detect duplicates.

但是，在唯一约束/索引中只允许一个NULL。因此，您必须使用触发器来检测重复项。

It's been requested from MS to ignore NULLS, but SQL 2008 has filtered indexes (as mentioned while I type this)

已经要求MS忽略NULLS，但是SQL 2008已经过滤了索引（正如我输入的那样）

#1