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:
ADD FOREIGN KEY CONSTRAINT in NOVALIDATE mode
- 1 See Filtering Modes
- 2 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
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.
- 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.
NOVALIDATE
keyword in any
of the following syntax contexts:- ALTER TABLE ADD column statements.
- ALTER TABLE INIT statements
- ALTER TABLE MODIFY statements.
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.
Establishing NOVALIDATE modes as the default
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 samedbimport
command.
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.
Example of a constraint created in NOVALIDATE mode
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.
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. 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.