SQL Server 2005 unique key with null value

Question

I have a table in SQL Server 2005 with a foreign key, and I want that foreign key to be a unique value or null. I've set it up as a unique key, but it won't allow me to have multiple nulls in the same table. Is it possible to do what I want?

Answer 1

This is a long time complaint about SQL Server's Unique constraints/indexes. The best solution is to create a view with schemabinding and then put a unique index on that column:

Create View dbo.MyUniqueColView
With SchemaBinding
As
Select MyColToBeUnique
From MyTable
Where MyColToBeUnique Is Not Null

GO

Create Unique Clustered Index IX_MyTable_MyColToBeUnique On MyUniqueColView ( MyColToBeUnique )

Answer 2

You cannot enforce this with a table level constraint in SQL Server 2005 but you can create a view with definition SELECT Col FROM t WHERE Col IS NOT NULL and then create a unique clustered index on that.