Contraint - unique or empty

Question

There is a nvarchar column in a table that allows empty values. Once there is a none-empty value it must be uniqe.

Is it possible to define this rule without a trigger?

EDIT:

http://decipherinfosys.wordpress.com/2007/11/30/multiple-null-values-in-a-unique-index-in-sql-serverdb2-luw/

is a nice workaround. The only issue I see is that the primary key might at some point be the same as one of the values in the column I want to be unique. Thats why I added a fixed string that I know never occurs in the constrained column.

Answer 1

There is a work-around for the multiple NULL values in a unique key column in http://decipherinfosys.wordpress.com/2007/11/30/multiple-null-values-in-a-unique-index-in-sql-serverdb2-luw/

Basically in the article they make an extra column that they keep unique. In case your original column is not null, its content is copied into the unique column. Whenever it is null, the primary key is copied into the column. This is a bit of a workaround, but it should work.

Answer 2

One solution that's not mentioned in the link tehvan posted is an indexed view. (Create a view excluding the rows with null column values and add a unique index on the view).

This approach may be preferred if you don't want to add additional columns to the base table. Both approaches need you to have the right ANSI settings enabled.