Thursday, January 10, 2008

Unique Column with Multiple NULLs

A very frequent need is to define unique column that allows multiple NULL values. Since the UNIQUE constraint considers two NULL values the same, it allows only a single NULL value.

Here is one solution for this problem using a view filtering on non NULL values with UNIQUE index on the view.

CREATE TABLE dbo.Foo (

 keycol INT NULL,

 datacol CHAR(1));

 

GO

 

CREATE VIEW UniqueFoo WITH SCHEMABINDING

AS

SELECT keycol, datacol

FROM dbo.Foo

WHERE keycol IS NOT NULL;

 

GO

 

CREATE UNIQUE CLUSTERED INDEX ix_UniqueFoo

ON UniqueFoo(keycol);

GO

 

-- OK, multiple NULL values allowed

INSERT INTO dbo.Foo VALUES(1, 'a');

INSERT INTO dbo.Foo VALUES(NULL, 'b');

INSERT INTO dbo.Foo VALUES(NULL, 'c');

 

GO

 

-- Error, attempt to insert duplicate keycol value

INSERT INTO dbo.Foo VALUES(1, 'd');

 

/*

 

Msg 2601, Level 14, State 1, Line 2

Cannot insert duplicate key row in object 'dbo.UniqueFoo' with unique index 'ix_UniqueFoo'.

The statement has been terminated.

 

*/



In SQL Server 2008 this can be accomplished much easier using UNIQUE filtered index.

CREATE UNIQUE NONCLUSTERED INDEX ix_Foo

  ON Foo (keycol)

  WHERE keycol IS NOT NULL;

1 comment:

Joe said...

cool stuff. I'll be implemented this today.

Thanks.