How do you create a Unique Contraint on non-null values only?

Question

Post reply

How do you create a Unique Contraint on non-null values only?

Tom257

SSChampion

Points: 11543
More actions
September 14, 2011 at 3:36 am

#146091

I have a table, created by a third party product. I want a unique constraint on a column, but only where values are not null. I will be updating values to non-null then updating them back to null once they are finished with. There will be thousands of non-null values in the table at any one time, and hundreds of thousands in the table with null values.
I tried creating a view selecting non null values and put a unique clustered index on that.
But the third party product also creates update/insert/delete triggers on the table, and these triggers are created with SET QUOTED_IDENTIFIER OFF. This means my indexed view does not work. the error is: "UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. "
I can change the triggers - but I'd rather not as updates to the third party product will change them back, and our DBA won't sanction it in case there are knock on effects. He won't allow CLR stuff in the database either.
So my question is, how can I enforce this unique constraint.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can create an indexed view filtering out NULL values and create a UNIQUE clustered index on the view.

I have done this before and I have to say it works well.

If you were using SQL2008, you could create a filtered index, but since this is the 2005 forum, I suspect you can't.

Hope this helps

Gianluca

-- Gianluca Sartori

Tom257 SSChampion Points: 11543 More actions · Answer 2

I tried that, but the third-party trigger settings on QUOTED_IDENTIFIER prevented any updates.

Msg 1934, Level 16, State 1, Procedure triggerUModifiedGroupAttribsORDERDATA, Line 2

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 3

Also google for "sql nullbuster"

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

My bad, I missed that part of your post.

I think the only possible solution is coding a trigger and ROLLBACK changes when a duplicate key is found.

It's not beautiful and clean, but I can't think of any other possible solution.

-- Gianluca Sartori

Tom257 SSChampion Points: 11543 More actions · Answer 5

Thanks,

I'm going with the trigger suggestion.

For completeness Mark's suggestion to try Googling sql nullbuster gave another method: Using a computed field - see the workarounds on this link

http://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values

But that didn't work in my situation, as the third party product barfs when computed columns are used :blink: I won't mention who supplies it - don't want to embarrass anyone here.

And I'm guessing its our own Hugo Kornelis who raised the issue with Microsoft.