Indexes for Constraints

Special indexes are created whenever you specify a unique, primary key, or referential constraint for a table. No user--including the table owner--can explicitly drop these system-generated constraint indexes, because they are used internally to enforce the constraints.

For primary key and unique constraints, the index is built on the constrained columns as a mechanism to detect duplicates as rows are added or updated.

For referential constraints, the index is built on the referencing columns of the constraint. This index ensures that the internal procedures that enforce the constraint when a referenced row is deleted or referenced columns are updated can be executed efficiently. When a referencing row is inserted or referencing columns are updated, the unique constraint index built on the referenced columns is used to ensure the efficiency of enforcing the constraint.

Note: If you create an index, and then create a constraint that uses the index, the index cannot be dropped (but the constraint can be dropped). If you create a constraint using the WITH INDEX=name clause but do not create the index (which causes the system to generate the named index), and you drop the constraint, the index is also dropped, because the index is a system index and not a user index.

You can specify options for constraint indexes by using the constraint WITH clause on CREATE TABLE.

In VDBA, the Table Level Unique Constraint dialog (accessible from both the Create Table and the Alter Table dialogs) has an Index button that allows you to fine tune the index used to enforce unique constraints. For additional information about the various dialog options, see online help.

These options give you more control over the index that is created, including the ability to specify:

Constraint indexes are, by default, stored in the default location for data files. Because of storage space or concurrency considerations, they can be stored in an alternate location.

By default, a B-tree storage structure is used for constraint indexes, but in some cases, a different structure can be more efficient. For more information on storage structures, see the chapter “Choosing Storage Structures and Secondary Indexes.”

You can save the overhead of generating an unnecessary index if you have an appropriate secondary index available. Simply indicate the name of the secondary index, and it is used to enforce the constraint instead of generating a new one.

To use an existing secondary index for referential constraints, the referencing columns must match the first n columns of the index (although not necessarily in order).

To use an existing secondary index for unique or primary key constraints, the index must be defined on exactly the same columns as the constraint, it must be a unique index, and it must specify that uniqueness is checked only after the UPDATE statement is completed.

Note: Indexes enforcing uniqueness constraints in the ANSI/ISO style, as required by referenced columns of a referential constraint, must specify the UNIQUE_SCOPE = STATEMENT option in the corresponding CREATE INDEX statement.

For more information on creating a secondary index and specifying the scope for uniqueness checking, see the chapter “Choosing Storage Structures and Secondary Indexes” and online help.

The index built for referential constraints is used only to improve the efficiency of the internal procedures that enforce the constraint when a referenced row is deleted or referenced columns are updated. Because the procedures can execute in its absence, the index is optional.

In the absence of a referential constraint index, the internal procedures use a secondary index, if one is available that is defined on the referencing columns, and so the efficiency of the procedures can not be compromised. However, if no such secondary index exists, a full-table scan of the referencing table is necessary. Thus, choosing not to generate a referential constraint must be reserved for special circumstances, such as when:

This option requires a table that uses a keyed storage structure. Because heap, which is a non-keyed structure, is the default when you create a table, this option cannot be specified for constraints added at that time. The ability to use the base table structure for constraint enforcement is available only for constraints that are added when altering an existing table.

Before you can specify the base table structure in lieu of a constraint index, you need to modify the table to change its storage structure type and to specify key columns to match the constraint columns it is used to enforce. If the base table structure is being used to enforce a primary key or unique constraint, you must also specify that uniqueness is checked only after the update statement is completed.

For more information on modifying storage structure of a table, specifying key values, and specifying the scope for uniqueness checking, see the chapter “Maintaining Storage Structures.”