Creating foreign-key constraints in NOVALIDATE modes

The ALTER TABLE ADD CONSTRAINT statement can create an enabled or filtering foreign-key constraint in a NOVALIDATE mode. The NOVALIDATE constraint modes prevent the database server from verifying that the foreign-key value in every row matches a primary-key value in the referenced table while the referential constraint is being created.

Use this syntax to create an enabled or filtering foreign-key constraint in NOVALIDATE mode:

Figure 1: ADD FOREIGN KEY CONSTRAINT in NOVALIDATE mode

Foreign key constraint in NOVALIDATE mode

Notes:

¹ See Filtering Modes
² Valid for FOREIGN KEY constraints only


Element	Description	Restrictions	Syntax
constraint	Name declared here for the constraint	Must be unique among constraint and index names in the database	Identifier
fk_col	Foreign-key column for constraint	Must exist in the child table	Identifier
pk_col	Primary-key column in the referenced table	Must exist in the referenced table	Identifier
pk_tab	Name of the referenced table	Must exist in the current database	Identifier
table, synonym	Table on which constraint is placed	Must exist in the current database	Identifier

Usage

This diagram omits the DISABLED keyword. Because disabled constraints are not checked for violations, the NOVALIDATE keyword is unnecessary in that case.

If no column or list of columns immediately follows the REFERENCES keyword, the default column (or columns) is the primary key of the pk_tab table. If pk_tab and the table or synonym specify the same table, then the constraint is self-referencing, and there is no default primary-key column.

If you declare no constraint name, the database server generates an identifier for the new constraint that it registers in the sysconstraints and sysobjstate system catalog tables.

The ALTER TABLE ADD CONSTRAINT statement supports the NOVALIDATE mode for referential constraints as a mechanism for bypassing the data-integrity check when creating an enabled or filtering referential constraint.

Circumstances where NOVALIDATE modes can improve performance

Although referential constraints protect data integrity, in some contexts a database table that you are moving to a new database server instance is known to be free of referential integrity violations. For foreign-key constraints on large tables, the time required to validate the constraint can be substantial. If a table with a million rows is moving from an OLTP environment to a data warehousing environment, validating the foreign key in the target environment might increase the time required for migration by orders of magnitude.

For example, you can drop the foreign-key constraints on the large table, and then re-create those constraints in an ENABLED NOVALIDATE mode or in a FILTERING NOVALIDATE mode immediately before you migrate the large table to the target database environment. The cost of the ALTER TABLE ADD CONSTRAINT operation that re-creates the foreign-key constraint will be relatively small, because it bypasses validation of every row for each referential constraint. Because the NOVALIDATE mode does not persist beyond the ALTER TABLE operation that created the constraints, the table arrives in the warehousing environment with those constraints in an ENABLED or FILTERING mode, protecting the referential integrity of the data in subsequent DML operations.

Restrictions on using the NOVALIDATE keyword

The ALTER TABLE ADD CONSTRAINT statement is the only DDL context where the NOVALIDATE keyword is valid when a foreign-key constraint is being created. You cannot, for example, create a foreign-key constraint in a NOVALIDATE mode in any of the following SQL statements:

CREATE TABLE statements
CREATE TEMP TABLE statements
SELECT INTO TABLE statements.

You can use the ALTER TABLE ADD CONSTRAINT statement to create an enabled constraint on an existing table in NOVALIDATE mode only if all of the following conditions are satisfied:

The constraint that you are adding is a foreign-key constraint. If you create multiple constraints in an ALTER TABLE statement that includes the NOVALIDATE keyword, all must be foreign-key constraints.
In ALTER TABLE statements, the NOVALIDATE keyword is valid only in the ADD CONSTRAINT FOREIGN KEY option.
The NOVALIDATE keyword is not valid for constraints that ALTER TABLE creates in DISABLED mode.

The ALTER TABLE statement fails with an error if the constraint definition includes the NOVALIDATE keyword in any of the following syntax contexts:

ALTER TABLE ADD column statements.
ALTER TABLE INIT statements
ALTER TABLE MODIFY statements.

The only other DDL statement where the NOVALIDATE keyword is valid is the SET CONSTRAINTS option to the SET Database Object Mode statement. While the SET CONSTRAINTS statement is running, it can change the mode of an existing foreign-key constraint to any of these NOVALIDATE constraint modes:

ENABLED NOVALIDATE mode
FILTERING WITH ERROR NOVALIDATE mode
FILTERING WITHOUT ERROR NOVALIDATE mode.

For more information, see the SET CONSTRAINTS statement.

Establishing NOVALIDATE modes as the default

Both the SET ENVIRONMENT NOVALIDATE ON statement of SQL and the dbimport -nv command for loading databases can override any foreign-key constraint mode (except DISABLED) that ALTER TABLE ADD CONSTRAINT or SET CONSTRAINTS statement specifies, if that constraint mode specification omits the NOVALIDATE keyword.

The scope of the SET ENVIRONMENT NOVALIDATE ON statement is subsequent ALTER TABLE ADD CONSTRAINT and SET CONSTRAINTS statements in the same user session.
The scope of the dbimport -nv command is the ALTER TABLE ADD CONSTRAINT and SET CONSTRAINTS statements in the .sql file of the exported database, whose path name is specified in the same dbimport command.

Just as when NOVALIDATE is specified explicitly in ALTER TABLE ADD CONSTRAINT and SET CONSTRAINTS statements, during the session that issued the SET ENVIRONMENT NOVALIDATE ON statement the default NOVALIDATE mode for foreign-key constraints persists

only while the foreign-key constraint is being created,
or only while an existing constraint is being reset to ENABLED or to FILTERING mode.

When the ADD CONSTRAINT or SET CONSTRAINTS statement completes execution without validating the foreign-key constraint, no record of the transient NOVALIDATE mode is created in the sysobjstate system catalog table. Instead, the database server registers the mode of the new or updated foreign-key constraint as ENABLED or as FILTERING in the sysobjstate.state column of its row in the system catalog.

Example of a constraint created in NOVALIDATE mode

The following DDL statements create a table called parent and define a unique index and a primary-key constraint on column c1 of that table:

CREATE TABLE parent(c1 INT, c2 INT, c3 INT);
CREATE UNIQUE INDEX idx_parent_c1 ON parent(c1);
ALTER TABLE parent ADD CONSTRAINT 
   PRIMARY KEY(c1) CONSTRAINT cons_parent_c1;

The next statements create another table, called child, whose first column is of the same data type as the primary-key column of the parent table, and define an enabled foreign-key constraint, called cons_child_x, on the child table:

CREATE TABLE child(x1 INT, x2 INT, x3 VARCHAR(32));
ALTER TABLE child 
   ADD CONSTRAINT (FOREIGN KEY(x1) 
       REFERENCES parent(c1) CONSTRAINT cons_child_x1);

Suppose that subsequent DML operations (not shown) populate the parent table and the child table with rows of data. At some point, the workflow requires the data to be moved from its OLTP production environment to another database for processing by business-analytics applications.

If at this point the data set in the child table contains a large number of rows, a significant cost of importing the child table to its new database will be validating the cons_child_x1 referential constraint. To avoid this cost. the following statement drops that constraint:

ALTER TABLE child DROP CONSTRAINT cons_child_x1;

After the child table has been exported to its new environment, the following statement can re-create a new constraint of the same name on the child table without checking every row for referential-integrity violations:

ALTER TABLE child
   ADD CONSTRAINT (FOREIGN KEY(x1)
      REFERENCES parent(c1)
         CONSTRAINT cons_child_x1 NOVALIDATE);

By default, the new cons_child_x1 referential constraint is in ENABLED mode after the ALTER TABLE statement completes execution.

Like the optional parentheses that can enclose various ALTER TABLE ADD CONSTRAINT syntax options, the parentheses enclosing the "Foreign key constraint in NOVALIDATE mode" syntax segment are optional. The next statement has the same effect as the previous NOVALIDATE example:

ALTER TABLE child
   ADD CONSTRAINT FOREIGN KEY(x1)
      REFERENCES parent(c1)
         CONSTRAINT cons_child_x1 NOVALIDATE;

Including the parentheses might make the logical structure clearer to human readers, but those delimiters have no effect on how the database server processes this ALTER TABLE ADD CONSTRAINT NOVALIDATE statement without validating each record.