Ensuring All Non-NULL Values Are Unique

[Sunite]

Ensuring All Non-NULL Values Are Unique



Q

I have a column in a SQL Server table that allows NULL values. I want this column to be unique when it has a value other than NULL. What's the best way to program this behavior? If I put a UNIQUE constraint on the column, I can include only one record that has a NULL value. I'm using a trigger to enforce this restriction, but can you recommend a simpler way to ensure that all non-NULL values are unique?



A


SQL Server has no built-in mechanism to prohibit duplicates except NULLs, so you need to implement a custom CHECK constraint to enforce this restriction. For example, the following code snippet enforces the kind of integrity you're looking for.

USE tempdb
CREATE table t1 (c1 int NULL, c2 char(5) NULL)
CREATE trigger mytrigger on t1 for insert, update as
BEGIN
IF (select max(cnt) from (select count(i.c1)
as cnt from t1, inserted i where t1.c1=i.c1 group
by i.c1) x) > 1
ROLLBACK TRAN
END

In SQL Server 2000, you can also use INSTEAD OF triggers to carry out this enforcement. For more information about INSTEAD OF triggers, see the articles below. To access these articles, go to SQL Server Magazine, enter the InstantDoc number in the InstantDoc box, and then click Go. The articles are:

 

Tricks with INSTEAD OF Triggers; InstantDoc number 15828

 

INSTEAD OF Triggers on Views; InstantDoc number 15791

 

INSTEAD OF Triggers; InstantDoc number 15524