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?

Walk

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 )

Kerstinkerwin

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.

Sweep

Answer 3

In SQL Server 2008, you could create a filtered index on the values that are not null - unfortunately, that is a new feature in the 2008 release, so in 2005, you're not able to do anything like that, I'm afraid.

Upstanding

Answer 4

Always an annoyance of mine. I prefer the "other" interpretation of the standard.

See Creating a unique constraint that ignores nulls in SQL Server. It covers three different approaches.

1. Solution 1: Filtered indexes.

   This seems pretty common, judging from other replies. I have not tried this approach -- it does require SQL Server 2008 though.

2. Solution 2: Constraint on computed column

   I have used this, and it does work. However, relationships may need to be adjusted and/or secondary non-unique covering indexed added (one for unique constraint, one for index cover).

3. Solution 3: UDF Check Constraint (Not recommended)

   Ahh, the power of TSQL (read: something else to deal with). I have not gone down this route.

Happy coding.

Hamid