UNIQUE or DISTINCT Option

Use the UNIQUE or DISTINCT keyword to require that the columns on which the index is based accept only unique data. If you do not specify the UNIQUE or DISTINCT keyword, the index allows duplicate values in the indexed column (or in the set of indexed columns). The following example creates a unique index:

CREATE UNIQUE INDEX c_num_ix ON customer (customer_num) 

A unique index prevents duplicate values in the customer_num column. A column with a unique index can have, at most, one NULL value.

The DISTINCT and UNIQUE keywords are synonyms in this context, so the following statement has the same effect as the previous example:

CREATE DISTINCT INDEX c_num_ix ON customer (customer_num) 

The index in both examples is maintained in ascending order, which is the default order.

You can also prevent duplicates in a column or set of columns by creating a unique constraint with the CREATE TABLE or ALTER TABLE statement. You cannot specify an R-tree secondary-access method for a UNIQUE index key. For more information on how to create unique constraints, see the CREATE TABLE or ALTER TABLE statements.

See also the section Differences Between a Unique Constraint and a Unique Index.

How Indexes Affect Primary-Key, Unique, and Referential Constraints

The database server creates internal B-tree indexes for primary-key, unique, and referential constraints. If a primary-key, unique, or referential constraint is added after the table is created, any user-created indexes on the constrained columns are used, if appropriate. An appropriate index is one that indexes the same columns that are used in the primary-key, referential, or unique constraint. If an appropriate user-created index is not available, the database server creates a nonfragmented internal index on the constrained column or columns.