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;

Labels: