Post reply

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

  • 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.

  • September 14, 2011 at 4:27 am

    #1381151

    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

  • September 14, 2011 at 4:37 am

    #1381153

    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.

  • September 14, 2011 at 4:50 am

    #1381158

    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

  • September 14, 2011 at 5:47 am

    #1381200

    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

  • September 14, 2011 at 6:25 am

    #1381221

    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.

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

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