Unique if Not Null

[fluteplr]

I need a way to check that a value in a column is unique if it is not null. There can (and must be, in this case) be any number of records where the column is null. But if the column is not null the value must be unique.

Using a unique constraint does not seem to work, since it only allows one record with a null value.

Any ideas?

 

[tlbroadbent]

Check the following link. It contains one person's solution.

http://groups.google.com/groups?selm=memo.20020121114941.1412B@a.a

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[fluteplr]

Thanks, Terry. Its better than nothing and it will sure beat tracking down the accidental duplicates.

 

[fluteplr]

I have to agree with his comment though. Why is it designed this way? I would not think that allowing one Null record would be useful in very many instances.