Creating and enabling referential constraints efficiently

When you create or enable foreign-key constraints on existing tables that contain data, you can sometimes achieve better performance by reducing the time that the database server spends searching for violating rows.

By maintaining the referential integrity of the database during DML operations, and by supporting efficient join-query execution paths on tables that are related by a star schema, foreign-key constraints can improve the performance of DML operations in databases where the primary key of each dimension table corresponds to a foreign key of the fact table.

When you use the ALTER TABLE ADD CONSTRAINT or ALTER TABLE MODIFY statement to define a foreign-key constraint on an existing table, you might be able to reduce the time required to validate of the new foreign-key constraint, if the referenced table already has a unique index or a primary-key constraint on the column corresponding to the key of the foreign-key constraint. When it creates a foreign-key constraint on a table that already contains data, the database server checks the table for any rows that violate the constraint. If an index exists, the database server makes a cost-based decision whether to scan every row in the table for violations, or to scan only the index valses.

For large tables, scanning only the index values can provide substantial performance improvement, unless one of the following requirements is not satisfied:
  • The ALTER TABLE statement is creating only one foreign-key constraint.
  • The ALTER TABLE statement is not also creating or enabling a CHECK constraint.
  • The ALTER TABLE statement is not also changing the data type of any existing column in the table.
  • The foreign-key columns do not include user-defined data types (UDTs) or built-in opaque data types.
  • The new mode of the foreign-key constraint is not DISABLED.
  • The table is not associated with an active violation table.

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 to scan the entire table tend to be proportional to the size of the table.

Enabling a foreign-key constraint using index-scan validation

To validate the enabled foreign-key constraint, the database server performs a full-table scan to search for violating rows, unless a unique index or a primary-key constraint already exists on the foreign-key column values. In that case, the database server consider using an index scan for validation, unless one or more of the following requirements is not satisfied:
  • The SET CONSTRAINTS statement is enabling only one foreign-key constraint.
  • The same statement is not enabling a CHECK constraint.
  • The foreign-key columns do not include user-defined data types (UDTs) or built-in opaque data types.
  • The new mode of the foreign-key constraint is not DISABLED.
  • The table is not associated with an active violation table.

Unless the table has 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 a full table scan can be substantial for very large tables.

Skipping validation of foreign-key constraints

In both the ALTER TABLE and SET CONSTRAINTS operations described above, the goal was to use a more efficient algorithm for validating the referential constraint. Greater efficiencies can be achieved, at least temporarily, by postponing or avoiding the validation of ENABLED or FILTERING foreign-key constraints that are being created by ALTER TABLE ADD CONSTRAINT statements, or while a DISABLED foreign-key constraint is being reset to an ENABLED or FILTERING mode.

This feature can be useful when tables that enforced referential constraints need to be moved from an OLTP environment to another database or to a data warehouse. To export the tables and restore their constraints without validation might be necessary if the time available for relocation is insufficient for violations checking. The tables might seem unlikely to include violating rows, if the constraints were dropped or disabled immediately before the tables were exported.

Three alternative mechanisms are available for bypassing the validation of enabled or filtering foreign-key constraints while they are being created, or while they are being exported, or while their mode is being changed from DISABLED:
  • You can include the NOVALIDATE keyword in the constraint mode specification
    • of the ALTER TABLE ADD CONSTRAINT statement,
    • or of the SET CONSTRAINTS ENABLED statement,
    • or of the SET CONSTRAINTS FILTERING WITH ERROR statement,
    • or of the SET CONSTRAINTS FILTERING WITHOUT ERROR statements.
  • If you plan to run multiple ALTER TABLE ADD CONSTRAINT or SET CONSTRAINTS statements, run the SET ENVIRONMENT NOVALIDATE ON statement to disable the validation of foreign-key constraints during the current session.

    Setting this session environment option makes NOVIOLATE the default mode for enabled or filtering referential constraints while the DDL statement is running.

  • If you are migrating data, include the -nv option in the dbimport command.

    The effect of the -nv command-line option is that the constraint modes of any ALTER TABLE ADD CONSTRAINT or SET CONSTRAINTS statements that create or enable foreign-key constraints are processed so that the ENABLED, or FILTERING WITH ERROR, or FILTERING WITHOUT ERROR constraint mode specifications are instead implemented (respectively) as the ENABLED NOVALIDATE, or FILTERING WITH ERROR NOVALIDATE, or FILTERING WITHOUT ERROR NOVALIDATE modes.

In each case, no constraint validation of existing rows occurs during the DDL statement.

The effect of the NOVALIDATE keyword or of the -nv command-line flag of dbimport does not persist outside the DDL operation that created or changed the mode of the foreign-key constraint. The same constraint enforces referential integrity during subsequent DELETE, INSERT, MERGE, and UPDATE operations. The NOVALIDATE mode of the referential constraint is not registered in the sysobjstate system catalog table.

If a NOVALIDATE constraint mode is used on a table that might already contains rows that violate the foreign-key constraint, it is the responsibility of the user to verify that no violating rows exist in the data.