Unique Constraints on Nullable Columns in SQL Server 2005

Question

In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered index as shown below but this is not available in earlier versions of SQL!

CREATE UNIQUE NONCLUSTERED INDEX User_UserName_IUC
ON [User] (pinNr)
WHERE UserName IS NOT NULL

But I don't think this is available in SQL Server 2005. In fact this blog post indicates there is a workaround using a trigger to check for uniqueness. Does anyone have an example of this? or maybe an alternative?

Unfortunately upgrading to SQl Server 2008 is not an option for this particular client!!

Answer 1

Actually, I had to do something like this once. It involved creating a computed column that takes the value of the Unique column when is not NULL and the value of the primary key (with some other logic to make it impossible to clash with the values on the unique column), and making the unique index on that column. You can see an example of this and the trigger method here.

Answer 2

You can make a view (Where Username is not null) then put a unique index against the view. You'll never need to use the view, it'll just exist for this.

You can also use this technique to enforce uniqueness across several tables, where it would normally be impossible.

• anon: Indexed views do not require enterprise edition. They do, however, require that you use the WITH (NOEXPAND) query hint to force the standard edition query optimizer to use the indexed view instead of the underlying query.
• Martin Smith: In this case though the only point of the view is to enforce the unique constraint and this this is a perfectly valid/common approach.
• Meff: Edited to remove incorrect info about Enterprise Edition being required.
• Jack Douglas: @Meff thanks for taking the time to respond to the comments here - I hope you won't be a stranger to the site: you might be interested in these questions :)