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.

insert into TableWithUniqueNullableColumn (UniqueCol) values (null)

insert into TableWithUniqueNullableColumn (UniqueCol) values (null)

insert into TableWithUniqueNullableColumn (UniqueCol) values (1)

insert into TableWithUniqueNullableColumn (UniqueCol) values (1)

 

[Update 30/7/2010] If you make the column persisted then you can reference the column with a foreign key.

Published Friday, July 30, 2010 11:19 AM by simonsabin

Comments

Friday, July 30, 2010 11:54 AM by GrumpyOldDBA

# re: Creating a unique constraint on a column that allows nulls

before sql 2008 you could fix this by creating an indexed view on the column where the value wasn't null. This was nifty way to cover things like NI numbers which have to be unique but new employees might not always know straight away, in fact this was the only way in 2000 unless you wanted to use a trigger.

# Twitter Trackbacks for Creating a unique constraint on a column that allows nulls - Simons SQL Blog [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Creating a unique constraint on a column that allows nulls - Simons SQL Blog         [sqlblogcasts.com]        on Topsy.com

Friday, July 30, 2010 12:20 PM by SimonS Blog on SQL Server Stuff

# Creating a unique constraint on a column that allows nulls

Primary keys, unique indexes and unique constraints all enforce uniqueness. The difference between the

Friday, July 30, 2010 1:42 PM by Matija Lah

# re: Creating a unique constraint on a column that allows nulls

If only we could do this properly - with a UNIQUE constraint...

That way the column(s) could also be referenced in foreign key constraints.

ML

Friday, July 30, 2010 2:01 PM by simonsabin

# re: Creating a unique constraint on a column that allows nulls

Foreign keys can reference unique indexes.

If you persist the column then you can reference the computed column with a forign key.

# Log Buffer #198, A Carnival of the Vanities for DBAs « Oracle Notes

Pingback from  Log Buffer #198, A Carnival of the Vanities for DBAs « Oracle Notes

# Log Buffer #198, A Carnival of the Vanities for DBAs « Oracle Notes

Pingback from  Log Buffer #198, A Carnival of the Vanities for DBAs « Oracle Notes

# Log Buffer #198, A Carnival of the Vanities for DBAs « Oracle Notes

Pingback from  Log Buffer #198, A Carnival of the Vanities for DBAs « Oracle Notes

Tuesday, August 24, 2010 9:13 PM by Matija Lah

# re: Creating a unique constraint on a column that allows nulls

Yes, you can reference a computed column in a foreign key constraint, but you cannot reference a column that only has a unique filtered index on it.

The point I was trying to make was that we also need filtered unique constraints.

E.g.:

alter table TableWithUniqueNullableColumn

add constraint UQ_TableWithUniqueNullableColumn_UniqueCol unique (UniqueCol) where UniqueCol is not null

Sorry, if I wasn't clear before.