Kenneth Fisher doesn’t like the way SQL Server implements unique key constraints:
So now that we all know the basics what could possibly have gone wrong? Well I was handed an error.
Msg 3723, Level 16, State 5, Line 21
An explicit DROP INDEX is not allowed on index ‘TblUniqueConstraint.uni_TblUniqueConstraint’. It is being used for UNIQUE KEY constraint enforcement.Someone had created a process several years ago that dropped and re-created indexes (I’m not going to go into why right now). Well this particular index is used to enforce a unique constraint and so it can’t be dropped. If you want to follow along here is some quick code to duplicate the problem.
The appropriate way to drop a unique key constraint is ALTER TABLE [TableName] DROP CONSTRAINT [ConstraintName].
I disagree with Kenneth that there’s no value in unique key constraints (I’m guessing implicit in here is “in comparison to using CREATE UNIQUE NONCLUSTERED INDEX” syntax). There’s a semantic difference between an index which happens to be unique versus a unique key constraint. They’re implemented very similarly, but the point of the latter is to tell anybody using the data model that this set of attributes must be unique.