Unique index or unique key?

fariba picture fariba · Sep 25, 2010 · Viewed 24.3k times · Source

What is the diffrence between a unique index and a unique key?

Answer

Dustin Laine picture Dustin Laine · Sep 25, 2010

The unique piece is not where the difference lies. The index and key are not the same thing, and are not comparable.

A key is a data column, or several columns, that are forced to be unique with a constraint, either primary key or explicitly defined unique constraint. Whereas an index is a structure for storing data location for faster retrieval.

From the docs:

Unique Index

Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique

Unique key (Constraint)

You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.