Unique Constraints vs Unique Indexes

Uniquely Portland

Uniquely Portland

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.

Previous Post
Estimated vs. Actual Number of Rows in Nested Loop Operators
Next Post
Deadlock Code for the WideWorldImporters Sample Database

Related Posts

9 Comments. Leave new

Thanks for sharing the link that talks about SET requirements for filtered Indexes! I was not aware of this. You are awesome!

Reply

I think unique constraints are aesthetically more appealing, and more obvious, at least in SSMS (and also the DDL). But I should admit I have a soft spot in my heart for little icons of upside-down blue keys :). Oh, and I hate NULLs (no matter what they might mean).

Reply

[…] Kendra Little discusses unique constraints versus unique indexes: […]

Reply

Database models should be self referential. Constraints are declarative and so embed in the model semantics that indexes (access) do not.

Reply

    This is what I think of as the ‘religious argument’ for constraints. It tends to get presented as the sole argument for unique constraints over unique indexes. I tried to present a more practical perspective in this post.

    Reply

Great post Kendra, thank you!
Just one question regarding reference of foreign keys: can foreign keys reference to filtered unique indexes as well? or they reference only the non-filtered indexes ?

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu