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.

Leave a Comment