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

问题描述

我使用的是 SQL Server 2005.

I'm using SQL Server 2005.

我有一个字段，它必须包含唯一值或 NULL 值.我想我应该使用 CHECK CONSTRAINT 或 TRIGGER for INSERT, UPDATE 来强制执行此操作.

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.

与触发器相比，在此处使用约束是否有优势(反之亦然)?这种约束/触发器可能是什么样的?

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?

推荐答案

这是使用约束来实现的另一种方法.为了强制执行此约束，您需要一个函数来计算字段值的出现次数.在您的约束中，只需确保此最大值为 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.

约束:

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

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

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

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

   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.

这篇关于字段值必须是唯一的，除非它是 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！