12

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!!

|improve this question
18

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.

|improve this answer
7

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.

|improve this answer
  • 2
    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. – Jeremiah Peschka Jan 15 '11 at 14:32
  • 3
    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. – Martin Smith Oct 30 '11 at 20:03
  • 2
    Edited to remove incorrect info about Enterprise Edition being required. – Meff Oct 31 '11 at 13:11
  • @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 :) – Jack Douglas♦ Oct 31 '11 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.