How to create unique index and how it works

Suppose you have a column (or combination of columns) that contains values that should always be unique. You want to create an index on this column (or combination of columns) that enforces the uniqueness and also provides efficient access to the table when using the unique column in the WHERE clause of a query.

When you create a unique key constraint, Oracle will automatically create an index for you. This is recommended approach for creating unique key constraints and indexes. This example creates a unique constraint named CUST_UX1 on the combination of the LAST_NAME and FIRST_NAME columns of the CUST table:

alter table cust add constraint cust_ux1 unique (last_name, first_name) using index tablespace users;

The prior statement creates the unique constraint, and additionally Oracle automatically creates an associated index. The following query displays the constraint that was created successfully:

How It Works

Defining a unique constraint ensures that when you insert or update column values, then any combination of non-null values are unique. Besides the approach we displayed in the above section, there are several additional techniques for creating unique constraints:

Use the CREATE TABLE statement.

• Create a regular index, and then use ALTER TABLE to add a constraint.

• Create a unique index and don’t add the constraint.

• These techniques are described in the next few subsections.

Use CREATE TABLE

Listed next is an example of using the CREATE TABLE statement to include a unique constraint.

create table cust(
cust_id number
,last_name varchar2(30)
,first_name varchar2(30)
,constraint cust_ux1 unique(last_name, first_name)
using index tablespace users);

The advantage of this approach is that it’s simple and encapsulates the constraint and index
creation within one statement.

Create Index First, Then Add Constraint

You have the option of first creating an index and then adding the constraint as a separate statement— for example:

SQL> create unique index cust_uidx1 on cust(last_name, first_name) tablespace users;

SQL> alter table cust add constraint cust_uidx1 unique (last_name, first_name);

The advantage of creating the index separate from the constraint is that you can drop or disable the constraint without dropping the underlying index. When working with large indexes, you may want to consider this approach. If you need to disable the constraint for any reason and then re-enable it later, you can do so without dropping the index (which may take a long time for large indexes).

Creating Only a Unique Index

You can also create just a unique index without adding the unique constraint—for example:

SQL> create unique index cust_uidx1 on cust(last_name, first_name) tablespace users;

When you create only a unique index explicitly (as in the prior statement), Oracle creates a unique index but doesn’t add an entry for a constraint in DBA/ALL/USER_CONSTRAINTS.

Why does this matter?

Consider this scenario:
SQL> insert into cust values (1, 'STARK', 'JIM');
SQL> insert into cust values (1, 'STARK', 'JIM');

Here’s the corresponding error message that is thrown:
ERROR at line 1:
ORA-00001: unique constraint (MV_MAINT.CUST_UIDX1) violated

If you’re asked to troubleshoot this issue, the first place you look is in DBA_CONSTRAINTS for a constraint named CUST_UIDX1. However, there is no information:

select
constraint_name
from dba_constraints
where constraint_name='CUST_UIDX1';

no rows selected

The “no rows selected” message can be confusing: the error message thrown when you insert into the table indicates that a unique constraint has been violated, yet there is no information in the constraint-related data-dictionary views. In this situation, you have to look at DBA_INDEXES to view the details of the unique index that has been created—for example: