Add UNIQUE constraint WITH NOCHECK ? Not really …

Ever wanted to add a UNIQUE constraint WITH NOCHECK ? Maybe you had some duplicates in your data, fixed the code, but you cannot remove the duplicates, or maybe it’s a “temporary” thing.

Well, you cannot have a UNIQUE constraint WITH NOCHECK, because these are actually unique indexes and these need to be checked. I had this problem where 2 columns were suppose to be a unique combination, and after quite some years, discovered that, who would’ve guessed … there are duplicates. We couldn’t just rush to delete data and add an unique constraint but we had to stop duplicates being added.
Having this table with data populated:

when I tried to add the unique constraint with nocheck on columns value_a and value_b:

boom:

Ok, the workaround is a scalar function which counts the values that I need to be distinct – in my case value_a and value_b:

and add a CHECK constraint, WITH NOCHECK, that checks the function doesn’t return a value greater than 1:

This way, i dont need to delete all duplicates right away, and I can evaluate the 3rd column in my table – specific_value – to see which one is correct and remove the duplicate.

And now, if I try to add another duplicate:

I’m getting the right boom:

I said above that UNIQUE constraints are indexes – let’s look at this. Let’s say we fixed our problem with duplicates, removed the CHECK constraint using the function and we add the right unique constraint:

When I check sys.indexes, I can find an entry there for my unique constraint, a NONCLUSTERED index:

Further, I insert a bunch of rows in this table:

And here’s the query plan generated for following query:

unique constraint index
My unique constraint is an index and I can see an INDEX SEEK operator on it.

This approach, with using a function in a check constraint, can be handy in many scenarios, not just this one, for example to validate complicated input or to validate data against other tables.

Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+

Leave a Comment