Unique Indexes

Specifying the keyword UNIQUE in the index creation statement creates an index in which uniqueness will be enforced throughout the table.The index is referred to as a “unique index”.A unique index is not a constraint.

Unique indexes cannot contain duplicate key values (or duplicate key value combinations, in the case of compound, or multi-column, or multi-segment) indexes.Duplicated NULLs are permitted, in accordance with the SQL standard, in both single-segment and multi-segment indexes.

Partial Indexes

Specifying the WHERE clause in the index creation statement creates a partial index (also knows as filtered index).A partial index contains only rows that match the search condition of the WHERE.

A partial index definition may include the UNIQUE clause.In this case, every key in the index is required to be unique.This allows enforcing uniqueness for a subset of table rows.

A partial index is usable only in the following cases:

The WHERE clause of the statement includes exactly the same boolean expression as the one defined for the index;
The search condition defined for the index contains ORed boolean expressions and one of them is explicitly included in the WHERE clause of the statement;
The search condition defined for the index specifies IS NOT NULL and the WHERE clause of the statement includes an expression on the same field that is known to exclude NULLs.