Jun 6, 2006

Creating unique constraint on columns with multiple null values

Before you read further: A more detailed and original post of this scenario is in the msdn blog: Achieving Selective Uniqueness in SQL Server Tables . Thanks to the comment from an anonymous reader. Please read through that if you need to understand the mechanics behind the suggestion. And it obviously is a better read as it comes from the creators of SQL Server :)
-Omni
-------------------------------------------------------------

Okay. The scenario is this. I have a table tlb1 and a nullable int column col1. I want to create an unique constraint on col1 satisfying the following requirements.
1. Col1 can have multiple nulls.
2. All non-null values in Col1 should be unique.


We are going to achieve this using indexed views.

Lets say this is the table script.

create table dbo.tbl1 (col1 int null)

Of course I am keeping the table script simple :)

Now I am going to enforce the custom unique constraint without touching the table schema.

Create a view like this on this table now. You need to set a few flags before you create an indexed view (check out the SET options while creating a view and an index in BOL). I am not giving it here.

And note that I am using "WITH SCHEMABINDING" option.

create view non_null_col_from_tbl1
with schemabinding
as
select col1 from dbo.tbl1
where col1 is not null


This view will give me all the non-null values in Col1. I will now create an index on col1 of this view. The script goes this way.

create unique clustered index idx1 on non_null_col_from_tbl1(col1)

Now, we are done.

Check it with this script.

insert into tbl1(col1) values(null)
insert into tbl1(col1) values(null)
insert into tbl1(col1) values(1)
insert into tbl1(col1) values(1)
insert into tbl1(col1) values(2)


Let me know if you guys have a better way of doing it.

3 comments:

Anonymous said...

http://blogs.msdn.com/sqlcat/archive/2005/12/20/506138.aspx

Omnibuzz said...

Nice.. Thanks for that link... Apologies if I hurt your sentiments. I come up with the blog posts based on the questions I get from the discussion forums or from peers
-Omni

Anonymous said...

One of The Simplest way to achieve this using SQL 2008 is:

CREATE UNIQUE NONCLUSTERED INDEX ON dbo.TableName(ColumnName)
WHERE ColumnName IS NOT NULL;
GO

Post a Comment