I ran into an interesting problem the other day. Given the title of the post, obviously a unique constraint was involved, although I want to point out that the problem was not caused by the constraint in and of itself. It did get me thinking about this particular kind of constraint and how it’s implemented.

To start with a unique constraint is just what it sounds like. It’s a constraint that forces the values of the column or columns in the constraint to be unique. Various points of interest include:

• The columns in a unique constraint can be NULL or NOT NULL. If a column is nullable then NULL is treated just like a value in terms of being unique. Ie a unique constraint on a single column will only allow at most one NULL value in the column.
• Unique constraints are implemented using unique indexes.
  • There must be room for the index to exist on the table. (There is a limit of 999 indexes as of SQL 2014 and currently on 2016 as far as I can tell.)
  • The unique constraint can be created as clustered or non-clustered (default). If it is created as clustered then it uses a clustered unique index and there can be only one of these.
• The Primary Key is special case of a unique constraint. There are some special limitations on the primary key.
  • The columns must be non-nullable.
  • There can be only one primary key whether or not it is clustered.

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.

CREATE TABLE TblUniqueConstraint (
Id INT NOT NULL IDENTITY(1,1) 
CONSTRAINT pk_TblUniqueConstraint PRIMARY KEY,
Col1 INT,
Col2 INT,
CONSTRAINT uni_TblUniqueConstraint 
UNIQUE CLUSTERED (Col1, Col2) 
)
GO
DROP INDEX TblUniqueConstraint.uni_TblUniqueConstraint 
GO

And this is why I prefer not to use unique constraints. There is no benefit and the down side is that instead of having one construct you have two (the constraint and the index). As you can see this can cause some unexpected side effects.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication Tagged: constraints, indexes, microsoft sql server