Unique indexes

DB2® uses unique indexes to ensure that no identical key values are stored in a table.

When you create a table that contains a primary key or a unique constraint, you must create a unique index for the primary key and for each unique constraint. DB2 marks the table definition as incomplete until the explicit creation of the required enforcing indexes, which can be created implicitly depending on whether the table space was created implicitly, the schema processor, or the CURRENT RULES special register. If the required indexes are created implicitly, the table definition is not marked as incomplete.

Restrict access with unique indexes

You can also use indexes to meet access requirements.

Example 1: A good candidate for a unique index is the EMPNO column of the EMP table. The following figure shows a small set of rows from the EMP table and illustrates the unique index on EMPNO.

Begin figure description. This figure shows a small set of rows from the EMP table and illustrates the unique index on EMPNO. End figure description. — Figure 1. A unique index on the EMPNO column

DB2 uses this index to prevent the insertion of a row to the EMP table if its EMPNO value matches that of an existing row. The preceding figure illustrates the relationship between each EMPNO value in the index and the corresponding page number and row. DB2 uses the index to locate the row for employee 000030, for example, in row 3 of page 1.

If you do not want duplicate values in the key column, create a unique index by using the UNIQUE clause of the CREATE INDEX statement.

Begin general-use programming interface information.

Example 2: The DEPT table does not allow duplicate department IDs. Creating a unique index, as the following example shows, prevents duplicate values.

CREATE UNIQUE INDEX MYINDEX
   ON DEPT (DEPTNO);

The index name is MYINDEX, and the indexed column is DEPTNO.

If a table has a primary key (as the DEPT table has), its entries must be unique. DB2 enforces this uniqueness by defining a unique index on the primary key columns, with the index columns in the same order as the primary key columns.

End general-use programming interface information.

Before you create a unique index on a table that already contains data, ensure that no pair of rows has the same key value. If DB2 finds a duplicate value in a set of key columns for a unique index, DB2 issues an error message and does not create the index.

If an index key allows nulls for some of its column values, you can use the WHERE NOT NULL clause to ensure that the non-null values of the index key are unique.

Unique indexes are an important part of implementing referential constraints among the tables in your DB2 database. You cannot define a foreign key unless the corresponding primary key already exists and has a unique index defined on it.

When not to use a unique index

In some cases you might not want to use a unique index. You can improve the performance of data access when the values of the columns in the index are not necessarily unique by creating a default index.

When you create a default index, DB2 allows you to enter duplicate values in a key column.

For example, assume that more than one employee is named David Brown. Consider an index that is defined on the FIRSTNME and LASTNAME columns of the EMP table.

CREATE INDEX EMPNAME ON EMP (FIRSTNME, LASTNAME);

This is an example of an index that can contain duplicate entries.

Tip: Do not create this type of index on very small tables because scans of the tables are more efficient than using indexes.

INCLUDE columns

Unique indexes can include additional columns that are not part of a unique constraint. Those columns are called INCLUDE columns. When you specify INCLUDE columns in a unique index, queries can use the unique index for index-only access. Including these columns can eliminate the need to maintain extra indexes that are used solely to enable index-only access.