Creating foreign-key constraints when an index exists on the referenced table

By default, the database server automatically validates enabled referential constraints when the ADD CONSTRAINT or MODIFY option to the ALTER TABLE statement includes the REFERENCES keyword to define a foreign-key constraint. You might be able to save time during validation of the new foreign-key constraint, if the referenced table already has a unique index or a primary-key constraint on the column (or on the set of columns) corresponding to the key of the referential constraint.

The database server makes a cost-based decision on how to validate the foreign-key constraint. The index-key algorithm might be faster in many contexts, because it validates the new constraint by scanning only the index values, rather than by scanning all the rows in the table.

The database server can consider using the index-key algorithm to validate the foreign-key constraint that it creates, but only if all of the following conditions are satisfied:
  • The ALTER TABLE statement is creating only one foreign-key constraint.

    If this is the case, the database server needs to check individual values for only the column on which the foreign-key constraint is being created. Validating two foreign-key constraints at the same time would require two indices to be used on the same scan, which is not supported.

  • The statement is not also creating or enabling a CHECK constraint.

    If the ALTER TABLE statement is creating more than one constraint, validating CHECK constraints requires that every row be checked, rather than individual values. In that case, the index-key algorithm cannot be used for validating the foreign-key constraint.

  • The statement that creates the foreign-key constraint does not also change the data type of any existing column in the same table.

    If the ALTER TABLE statement that creates the foreign-key constraint includes a MODIFY clause that changes the data type of any column, the database server does not consider an index-scan execution path for validating the constraint.

  • The foreign-key columns do not include user-defined data types (UDTs) or built-in opaque data types.

    To make the fast index-key algorithm as efficient as possible, it eliminates all the inefficiencies of executing routines associated with user-defined or opaque data types, such as the BOOLEAN and LVARCHAR built-in opaque types.

  • The mode of the new foreign-key constraint is not DISABLED.

    If it is disabled, then no constraint-checking algorithm is needed, because no checking for referential integrity violations occurs.

  • The table is not associated with an active violation table.

    Violations tables require that at the time of checking, every row that does not satisfy the new constraint must be inserted into the violation table. Scanning every row for violations prevents the database server from using the faster index-key algorithm that skips duplicate rows.

Except in the case of one or more violating rows, the ALTER TABLE ADD CONSTRAINT or ALTER TABLE MODIFY statement can create and validate a foreign-key constraint when some of these requirements are not satisfied, but the database server will not consider using the index-key algorithm to validate the foreign-key constraint. The additional validation costs for scanning the entire table are generally proportional to the size of the table. These costs can be substantial for very large tables.

When you create a self-referencing foreign-key constraint, whose REFERENCING clause specifies the same table on which the constraint is defined, the database server can consider an index-key algorithm for validating referential-integrity, if all of the conditions listed above are satisfied.