Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 15 down vote accepted

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.

share|improve this answer
    
I believe you can do this same technique with a unique constraint on a computed column, thus avoiding the need for a trigger. –  John Bledsoe Jan 14 '11 at 14:32
    
@John Bledsoe Yes you can, actually. In fact, the technique I explained was that of a computed column, the link I provided explains both the computed column and the trigger technique. –  Lamak Jan 14 '11 at 15:25
    
You're right. I just saw trigger and commented...not enough coffee. –  John Bledsoe Jan 14 '11 at 16:51

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.

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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