Field value must be unique unless it is NULL

问题:

I'm using 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.

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.

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.

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.

回答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.

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.

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.

回答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.

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

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

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:

 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:

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

回答4:

In Oracle, a unique key will permit multiple NULLs.

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

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.

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/

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

回答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.

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