When should I use a unique constraint instead of a unique index?

Question

When I want a column to have distinct values, I can either use a constraint

create table t1(
id int primary key,
code varchar(10) unique NULL
);
go

or I can use a unique index

create table t2(
id int primary key,
code varchar(10) NULL
);
go

create unique index I_t2 on t2(code);

Columns with unique constraints seem to be good candidates for unique indexes.

Are there any known reasons to use unique constraints and not to use unique indexes instead?

Answer 1

Under the hood a unique constraint is implemented the same way as a unique index - an index is needed to efficiently fulfill the requirement to enforce the constraint. Even if the index is created as a result of a UNIQUE constraint, the query planner can use it like any other index if it sees it as the best way to approach a given query.

So for a database that supports both features the choice of which to use will often come down to preferred style and consistency.

If you are planning to use the index as an index (i.e. your code may rely on searching/sorting/filtering on that field to be quick) I would explicitly use a unique index (and comment the source) rather than a constraint to make that clear - this way if the uniqueness requirement is changed in a later revision of the application you (or some other coder) will know to make sure a non-unique index is put in place of the unique one (just removing a unique constraint would remove the index completely). Also a specific index can be named in an index hint (i.e. WITH(INDEX(ix_index_name)), which I don't think is the case for the index created behind the scenes for managing uniqueness as you are unlikely to know its name.

Likewise if you are only needing to enforce uniqueness as a business rule rather than the field needing to be searched or used for sorting then I'd use the constraint, again to make the intended use more obvious when someone else looks at your table definition.

Note that if you use both a unique constraint and a unique index on the same field the database will not be bright enough to see the duplication, so you will end up with two indexes which will consume extra space and slow down row inserts/updates.

Answer 2

One of the major differences between a unique constraint and a unique index is that a foreign key constraint on another table can reference columns that make up a unique constraint. This is not true for unique indexes. In addition, unique constraints are defined as part of ANSI standard, while indexes are not. Finally, unique constraint in considered to live in the realm of logical database design (which may be implemented differently by different DB engines) while index is physical aspect. Therefore, unique constraint is more declarative. I'd prefer unique constraint in almost all cases.

Answer 3

UNIQUE Constraint is preferred over UNIQUE Index. When the constraint is not unique you need to use a regular or non unique index. Constraint is also another type of index. Index is used for faster access.

Unique Indexes can have where clauses. For example, you can create indexes for every year based on the date column

WHERE Sale_Date BETWEEN '2012-01-01' AND '2012-12-31'

Ravi Ramaswamy