What is the difference between a unique constraint and a unique index

Question

What is the difference between the following two statements?

alter table [dbo].[Demo] add constraint [UC_Demo_Code] unique ( [Code] )
go
create unique nonclustered index [UK_Demo_Code] on [dbo].[Demo] ( [NB_Code] )
go

Does the constraint have an index to help it enforce uniqueness, or will a table scan be performed on every insert/update?

Deborahdeborath

Check out dba.stackexchange.com/a/55139/6548 for a thorough answer. – Mucosa

Answer 1

The "logical" effect is the same -- only unique values may be loaded into the table. (It's worth mentioning that if the column is nullable, only 1 row with NULL may be inserted.)

The physical effect is the same -- a unique index is built on the table. It could be either clustered or nonclustered.

The only real difference is in the metadata, the information describing the database as stored in the system tables. The first way, it is recorded internally as an index, and the second, it is recorded as a constraint -- even though the net effects are identical. Which means that, ultimately, the only difference is the code required to create it AND to alter it in the future.

(This is true for SQL 7.0 through 2005, and I'd be very surprised if they changed it in 2008).

Corron

It's worth noting that a unique index can be filtered e.g., create unique nonclustered index IX_relation ON dbo.Person(relation) where relation is not null. This is not possible with a unique constraint. – Tum

Very true. Filtered indexes were new in 2008; this answer dates back to early 2010, and I'm fairly sure I did not have access to it yet. These days, since the unique constraint creates an index, I would say "call an index and index and be done with it." – Corron

Answer 2

You can Check Unique Constraints and Unique Indexes for a comparison between them.

The article concludes that there's no practical difference between a unique constraint and a unique index other than the fact that the unique constraint is also listed as a constraint object in the database. Since a unique constraint can't be disabled, having the status of a constraint doesn't give the unique constraint any additional behavior beyond a unique index. However, there are several index creation options that aren't available to the ALTER TABLE command that creates a unique constraint.

Compensable

Answer 3

Excuse me for my first incorrect answer. Unique constraint is identical to unique index. Under the covers, it does create an index.

Devolve