Creating Unique Indexes- Tables and Constraints

When you create a B-tree index, you can also specify that the index be unique. Doing so ensures that non-NULL values are unique when you insert or update columns in a table.

Suppose you have identified a column (or combination of columns) in the table (outside the primary key) that is used heavily in the WHERE clause. In addition, this column (or combination of columns) has the requirement that it be unique within a table.

This is a good scenario in which to use a unique index. Use the UNIQUE clause to create a unique index:

SQL> create unique index cust_uk1 on cust(first_name, last_name);

The unique index does not enforce uniqueness for NULL values inserted into the table. In other words, you can insert the value NULL into the indexed columns for multiple rows.

You must be aware of some interesting nuances regarding unique indexes, primary key constraints, and unique key constraints (see Chapter 7 for a detailed discussion of primary key constraints and unique key constraints).

When you create a primary key constraint or a unique key constraint, Oracle automatically creates a unique index and a corresponding constraint that is visible in DBA/ALL/USER_CONSTRAINTS.

When you create a unique index explicitly (as in the example in this section), Oracle

creates a unique index but does not add an entry for a constraint in DBA/ALL/USER_ CONSTRAINTS. Why does this matter? Consider this scenario:

SQL> create unique index cust_uk1 on cust(first_name, last_name); SQL> insert into cust values(500,’JOHN’,’DEERE’), (501,’JOHN’,’DEERE’);

Here is the corresponding error message that is thrown:

ERROR at line 1:

ORA-00001: unique constraint (MV_MAINT.CUST_UK1) violated

If you are asked to troubleshoot this issue, the first place you look is in DBA_ CONSTRAINTS for a constraint named CUST_IDX1. However, there is no information:

SQL> select constraint_name

from dba_constraints  where constraint_name=’CUST_UK1′;

Here is the output:

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 and DBA_IND_COLUMNS to view the details of the unique index that has been created:

SQL> select a.owner, a.index_name, a.uniqueness, b.column_name from dba_indexes a, dba_ind_columns b

where a.index_name=’CUST_UK1′

and a.table_owner = b.table_owner and a.index_name = b.index_name;

If you want to have information related to the constraint in the DBA/ALL/USER_ CONSTRAINTS views, you can explicitly associate a constraint after the index has been created:

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

In this situation, you can enable and disable the constraint independent of the index. However, because the index was created as unique, the index still enforces uniqueness regardless of whether the constraint has been disabled.

When should you explicitly create a unique index versus creating a constraint and having Oracle automatically create the index? There are no hard-and-fast rules. I prefer creating a unique key constraint and letting Oracle automatically create the unique

index, because then I get information in both the DBA/ALL/USER_CONSTRAINTS and DBA/ ALL/USER_INDEXES views.

But, Oracle’s documentation recommends that if you have a scenario in which you are strictly using a unique constraint to improve query performance, it is preferable to create only the unique index. This is appropriate. If you take this approach, just be aware that you may not find any information in the constraint-related data dictionary views.