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.
Comments closed