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

By default, the database server automatically validates referential constraints when their mode is changed to ENABLED. You might be able to save time when the SET CONSTRAINTS statement enables a 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 foreign-key constraint.

The database server makes a cost-based decision on how to validate the enabled foreign-key constraint. The index-key algorithm might be faster in many contexts, because it validates the 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 enables, but only if all of the following conditions are satisfied when the SET CONSTRAINTS ENABLED statement resets the constraint mode:
  • The SET CONSTRAINTS statement is enabling 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 enabled. 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 same statement is not enabling a CHECK constraint.

    If the SET CONSTRAINTS statement is enabling 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 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 built-in opaque data types, such as the BOOLEAN and LVARCHAR built-in opaque types.

  • The new mode of the 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 SET CONSTRAINTS statement can enable 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 enable 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 the conditions listed above are satisfied.