on September 8, 2016
Unique constraints and unique nonclustered indexes have a lot in common: unique constraints are implemented with a unique index behind the scenes.
While unique nonclustered indexes have a couple of additional features, I can still think of a scenario where it might be best to use unique constraints.
Unique Indexes: More flexible!
1. You can add included columns to a unique index
Included columns can potentially make unique indexes more useful to support query performance. Uniqueness is only enforced for the key columns defined in the index – so adding the included columns doesn’t change what is required to be unique.
2. You can add a filter to a unique index - particularly handy when it comes to NULLs
SQL Server is a little peculiar when it comes to NULLS and uniqueness: it considers NULL to be a value. If I create a unique constraint on the nullable column ‘Foo’, I can have only one row where Foo is NULL.
If you need to have multiple rows where Foo is NULL, but want to enforce uniqueness for non-null rows, you can create a filtered unique index where Foo IS NOT NULL.
3. Foreign keys can reference non-filtered unique indexes
You can also define foreign keys that reference a unique index, just like you can against a primary key constraint or a unique constraint.
So given these things, is there still a case for sometimes using unique constraints? I think so.
Unique Constraints: Potentially safer from accidental drops
Some developers need to design a schema for a database where they won’t always control the index tuning. The database may be distributed as part of software that is managed by others.
One potential problem with unique indexes is procedural: a unique index might be accidentally dropped by a DBA who is working to reduce the amount of indexes in a database. The DBA may just not notice that the indexes are enforcing uniqueness if they write a script quickly to find “unused” indexes and forget to exclude unique indexes.
However, if you try to drop a unique constraint with a ‘drop index’ command, you’ll get an error. You need to remove it with an ALTER TABLE DROP CONSTRAINT command– which is a bit less likely to be run by accident. In this case, using unique constraints might be potentially safer, as far as user error is concerned.