Using unique constraints
10December 10, 2015 by Kenneth Fisher
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.
SQL Studies 
[…] Kenneth Fisher doesn’t like the way SQL Server implements unique key constraints: […]
At the end you say there is no benefit to unique constraints.
So what do you do when you have a column and you want the values to be unique?
No benefit to a unique constraint vs a unique index. A unique index will do just as much for you but without the extra construct.
You’re wrong about there being no benefit to a unique constraint. A unique constraint can serve as the parent for a foreign key. If Table_A has a unique constraint, child Table_B can have a foreign key that refers back to the unique constraint in Table_A, rather than to Table_A’s primary key. I’ve found this to be useful several times over the years.
That’s still not a benefit of a unique constraint over a unique index. A unique index can also be used as a parent for a foreign key. The constraint itself isn’t necessary.
Using the UNIQUE constraint shows that the programmer understands abstract concepts and the idea of declarative language. Using a unique index shows that the programmers mindset is still back in file systems and not yet thinking in terms of sets and RDBMS. The constraint shows the mentality of WHAT, the index is HOW.
For historical reasons, we can only have one primary key (a leftover from sequential file systems) in a table. However, there is no limit to the number of UNIQUE constraints. You can put on a table. And they can overlap! I have an article on how to use multiple unique constraints to put complex logic into tables using declarative programming. I do not know a publication date for it yet.
While I agree in general terms and certainly in theoretical ones, in the specific case of SQL Server I still don’t feel that the constraint adds anything over the index. And in fact adds an extra construct that can cause difficulties when working with automated process that aren’t specifically designed to check for them.
Doesn’t the error message help an outsider easily identify what’s gone wrong?
Isn’t that another good reason for the constraint?
The error is going to be pretty similar for an index or a constraint so I don’t really see h
[…] all the data from the saved-to-disk files Hit-and-Run Driver Arrested Because Car Reported Accident Using unique constraints Database Design […]