Default Index Creation Strategy for Constraints

When you create a table with unique or primary-key constraints, the database server creates a system-generated, internal index that is unique and ascending for each constraint.

When you drop a primary-key or foreign-key constraint, the corresponding system-generated index is also dropped. However, you can preserve the index by renaming it with the RENAME INDEX statement.

When you create a table with a referential constraint, the database server creates an internal index that is ascending and that allows duplicate values for each column that you specify in the referential constraint.

An internal index occupies the same storage location as its table. For fragmented tables, the fragments of an internal index occupy the same dbspace partitions that you specify for the table fragments (or in some cases, the database dbspace).

If you require an index fragmentation strategy that is independent of the underlying table fragmentation, do not define the constraint when you create the table. Instead, use the CREATE INDEX statement to create a unique index with the desired fragmentation strategy. Then use the ALTER TABLE statement to add the constraint. The new constraint uses the previously defined index.
Important: In a database without logging, detached checking is the only kind of constraint checking available. Detached checking means that constraint checking is performed on a row-by-row basis.