Unique index or unique key?

Question

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

Answer 1

The unique piece is not where the difference lies. The index and key are not that 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 denied unique constraint. Whereas an index are structures 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.

Answer 2

This MSDN article comparing the two is what you're after. The terminology is such that "constraint" is ANSI, but in SQL Server you can't disable a Unique Constraint...

For most purposes, there's no difference - the constraint is implemented as an index under the covers. The MSDN article backs this up--the difference is in the meta-data, for things like:

• tweaking FILLFACTOR
• INCLUDE provides more efficient covering indexes (composite constraint)
• A filtered index is like a constraint over a subset of rows/ignore multiple null etc.

Answer 3

"Unique key" is a tautology. A Key (AKA "Candidate Key") is logical feature of the database - a constraint that enforces the uniqueness of a set of attributes in a table.

An index is a physical level feature intended to optimise performance in some way. There are many types of index.

Answer 4

Hi,

Unique Key: It is a constraint which imposes limitation on database. That limitation is it will not allow duplicate values . For example if you want to select one column as primary key it should be NOT NULL & UNIQUE.

Unique Index: It is a index which improves the performance while executing queries on your data base. In unique index it also not allows duplicate values in index . ie.no two rows will have the same index key value.

To clear your doubts on keys,constrains & indexes try some free online classes at the following link. http://www.wiziq.com/tutorial/56279-Indexes-in-SQL

Thanks Regards

Kolla Sanjeeva Rao

Answer 5

Both the key (aka keyword) and index are identifiers of a table row.
Though index is parallel identification structure, containing a pointer to the identified row, while keys are in situ field members.

The key, as identifier, implies uniqueness (constraint) and NOT NULL (constraint). There is no sense in NULL as identifier (as null cannot identify anything) as well nonunique identifying value.
Non-clustered index can contain real data, not serving as identifier to real data, and so be non-unique [1]

It is unfortunate practice that the key or index (identifier) is called by constraint (rule or restriction) what most previous answers here followed.

Keys are used in context of:

• alternate aka secondary aka candidate keys, can be multiple
• composite key (a few fields combined)
• primary key (superkey), natural or surrogate key, only one, really used for referential integrity
• foreign key

Foreign key is the key in another table (where it is primary key) and even not a key to which they frequently refer. Such use is explained by confusing shortcutting of "foreign key constraint" term to just "foreign key".

Primary key constraint really implies NOT NULL and UNIQUE constraints + that referenced column (or combined columns) is identifier and also unfortunately substituted by "primary key" or "primary key constraint" while it is both which cannot be called either by only (primary key) constraint or by only (primary) key.

---

Update:
My related question:
[1]
UNIQUE argument for INDEX creation - what's for?