Creating a unique constraint on a column that allows nulls

Primary keys, unique indexes and unique constraints all enforce uniqueness. The difference between the primary key and the unique index/constraint is that the primary key does not allow any NULL values. However whilst the unique index/constraints do allow null values they only allow one of them. Well thats obvious isn’t it. If you have 2 NULL values then you have duplicates and that breaks the uniqueness.

Lets try it

create table TableWithUniqueNullableColumn (PkID int identity(1,1), UniqueCol int)
go
alter table TableWithUniqueNullableColumn add constraint UQ_TableWithUniqueNullableColumn_UniqueCol unique (UniqueCol )

insert into TableWithUniqueNullableColumn (UniqueCol) values (null)
insert into TableWithUniqueNullableColumn (UniqueCol) values (null)

The second insert will fail :(

So how do you enforce uniqueness AND have multiple NULL values.

Well on SQL 2008 its easy you just have a filtered index that excludes NULL values.

create unique index UQ_TableWithUniqueNullableColumn_UniqueCol on TableWithUniqueNullableColumn(UniqueCol) where UniqueCol is not null

Now you should be able to insert multiple null values

insert into TableWithUniqueNullableColumn (UniqueCol) values (null)
insert into TableWithUniqueNullableColumn (UniqueCol) values (null)
Ok thats fine but what about SQL 2005.
Well there is a trick, you can use a computed column that is the same values as your unique column unless it is null in which case it uses the PK value for your table.

alter table TableWithUniqueNullableColumn add SurrogateUniqueCol as isnull(UniqueCol,-PkID)
alter table TableWithUniqueNullableColumn add constraint UQ_TableWithUniqueNullableColumn_UniqueCol unique (SurrogateUniqueCol )
You will see that I am using –PKID this provides protection for overlapping values. It is up to you to make sure the values in your backup column, in this case PKID) don’t overlap with the value in your unique column. You could add a prefix or some other way of ensuring no duplicates.

Now with that in place you can insert null values and if you try and insert duplicate non null values it will fail.

Just try this, the first 3 should work and the last one should fail.

Read more: Simons SQL Blog

Posted via email from .NET Info