Creating indexes to improve referential integrity performance for foreign keys

When you define foreign keys, you can improve the performance of certain operations by creating indexes that match the columns of the foreign key.

About this task

For operations that require referential integrity checks, Db2 uses available indexes to improve the performance of the checking operations. For primary keys, the indexes are required for referential integrity, so they are always available for Db2 to use. However, indexes are not required for foreign keys, but you can create them to improve the performance of the checks for operations on the parent table.

Introductory concepts

Procedure

For rows of a parent table that are frequently deleted, create indexes that support the performance of referential integrity:

Create an index on the columns of a foreign key.
Db2 can use the index to improve the performance of the operations that check the validity of the DELETE statement and its possible effect on the dependent table. Use the following information to help you plan your approach:
  • For the index to qualify, the leading columns of the index must be identical to and in the same order as all columns in the foreign key. The index can include more columns, but the leading columns must match the definition of the foreign key.
    Restriction: Indexes that use expressions cannot be used for this purpose.
  • A foreign key can also be the primary key. In that case, the primary index is also a unique index on the foreign key, and every row of the parent table has at most one dependent row. The dependent table might be used to hold information that pertains to only a few of the occurrences of the entity that is described by the parent table. For example, a dependent of the employee table might contain information that applies only to employees working in a different country.
  • If the first n columns of the foreign key are the same as the columns of the primary key, the primary key can share columns of the foreign key.

For example, the following CREATE TABLE statement specifies constraint names REPAPA and REPAE for the foreign keys in the employee-to-project activity table.

CREATE TABLE DSN8C10.EMPPROJACT
      (EMPNO     CHAR(6)          NOT NULL,
       PROJNO    CHAR(6)          NOT NULL,
       ACTNO     SMALLINT         NOT NULL,
       CONSTRAINT REPAPA FOREIGN KEY (PROJNO, ACTNO)
               REFERENCES DSN8C10.PROJACT ON DELETE RESTRICT,
       CONSTRAINT REPAE FOREIGN KEY (EMPNO)
               REFERENCES DSN8C10.EMP ON DELETE RESTRICT)
  IN DATABASE DSN8D12A;

In the sample project activity table, the primary index (on PROJNO, ACTNO, ACSTDATE) serves as an index on the foreign key on PROJNO. It does not serve as an index on the foreign key on ACTNO, because ACTNO is not the first column of the index.