To start with there is no practical difference between Unique Index and unique constraint; they both accomplish the task of providing uniqueness to a column.

A unique constraint is enforced by creating a unique index under the hood. Let’s create a unique index and unique constraint on a column and analyze the metadata.

The above query creates a unique index unique_index_name and a unique constraint unique_constraint_name on Person.Addresstype.Name column. As mentioned above that a unique constraint is enforced by a unique index, let’s analyze indexes on Person.Addresstype table.

1_unique index vs unique constraint sql server

The only difference we can see in above image is that constraint index – index_description includes “unique key” keyword, rest all is same.

A unique constraint can’t be disabled like foreign key and check constraint however, the unique index even the one enforced by unique constraint can be disabled as shown below.

Let’s verify whether its disabled or not.

2_unique index vs unique constraint sql server

To enable the index execute the below query.

As compared to unique constraints, unique index gives more options in terms of Fill Factor, included columns etc. however, it’s just that unique constraint clear the intentions that it is a constraint where as a unique index doesn’t, this is because the word constraint literally means that value is being checked whereas index is related to increasing performance. It’s up to you to select any of these to enforce unique constraint on a column.



Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook