NOVALIDATE session environment option

Use the NOVALIDATE environment option to specify whether a foreign-key or check constraint that the ALTER TABLE ADD CONSTRAINT statement creates, or that has its constraint mode reset by the SET CONSTRAINTS statement, are in NOVALIDATE mode by default. The NOVALIDATE setting has no effect, however, on ADD CONSTRAINT or SET CONSTRAINT operations that specify DISABLED mode.

NOVALIDATE environment option

SET ENVIRONMENT NOVALIDATE { { '1' | ON } | { '0' | OFF } }

Usage

Enabling this session environment variable can prevent referential-integrity checking of the foreign-key constraint or checking the condition of the check constraint during these subsequent SQL statements:

  • ALTER TABLE ADD CONSTRAINT
  • SET CONSTRAINTS ENABLED
  • SET CONSTRAINTS FILTERING

Bypassing validation during these data definition language (DDL) operations can improve the performance of the database server in contexts where there is no reason to expect integrity or check condition violations, or where validation of constraints can be postponed until after the tables are relocated to another database.

To set the NOVALIDATE session environment variable, specify one of these values:
'1' or ON
You do not need to explicitly include the NOVALIDATE keyword to bypass validation of the ENABLED or FILTERING constraint while either of those DDL statements is running.
'0' or OFF
This restore the default behavior of those DDL statements, so that the database server automatically checks the table for constraint violations during the ALTER TABLE or SET CONSTRAINTS operation that created or enabled the constraint.
Note: Whether or not the SET ENVIRONMENT NOVALIDATE session environment option is enabled, any NOVALIDATE attribute that the ALTER TABLE ADD CONSTRAINT statement or the SET CONSTRAINTS option of the SET Database Object Mode statement applied to the object mode of a constraint is automatically dropped after execution of that DDL statement completes. The mode of the constraint becomes whatever the SET CONSTRAINTS or ALTER TABLE statement registered in the sysobjstate system catalog table, which ignores the NOVALIDATE attribute.

While you are creating ENABLED or FILTERING constraints with the ALTER TABLE ADD CONSTRAINT statement, or changing the mode of a constraint to ENABLED or FILTERING with the SET CONSTRAINTS statement, the NOVALIDATE option prevents the database server from checking for constraint violations while the ALTER TABLE or SET CONSTRAINTS statement is running. That can save significant time in moving large tables.

For example, the following statement enables the NOVALIDATE session environment variable: 
SET ENVIRONMENT NOVALIDATE '1';

It has these subsequent effects during the following DDL operations on foreign-key or check constraints in the database to which the current session is connected:

  • SET CONSTRAINTS options of the SET Database Object Mode statements for constraints change the default or explicit constraint mode to include NOVALIDATE, unless DISABLED is specified as the constraint mode.
  • Constraints that the ALTER TABLE ADD CONSTRAINT statement specifies with no explicit mode are created in ENABLED NOVALIDATE mode by default.
  • Constraints that the ALTER TABLE ADD CONSTRAINT statement specifies in ENABLED or in FILTERING mode are also in NOVALIDATE mode by default.
The following example restores the default constraint mode behavior:
SET ENVIRONMENT NOVALIDATE OFF;

For subsequent SET CONSTRAINTS or ALTER TABLE ADD CONSTRAINT statements that omit the NOVALIDATE keyword from the object mode of a constraint, the database server validates the referential integrity or check conditions of the table by performing a full-table scan or an index scan. For tables with a million rows, for example, the cost of this validation is substantial.

Suspending constraint-checking during SET CONSTRAINTS or ALTER TABLE ADD CONSTRAINT statements by enabling the NOVALIDATE session environment option can be efficient for tables that have been populated by OLTP operations that enforced the same constraints. After moving those tables to another database or to a data warehouse with their constraints dropped or disabled, you can use the SET ENVIRONMENT NOVALIDATE ON statement to avoid the overhead of checking for violations while the constraints are being restored.

Examples of setting NOVALIDATE

Like all SQL keywords, the ON and OFF settings are case insensitive.

Double ( " ) and single ( ' ) quotation marks are both valid as delimiters for the "1" and "0" settings, but both delimiters of a numeric setting must be the same.

For example, to make NOVALIDATE a default keyword in subsequent DDL operations setting the mode of constraints within the scope of this session environment option, use any of the following statements: 
SET ENVIRONMENT NOVALIDATE '1';
SET ENVIRONMENT NOVALIDATE ON;
SET ENVIRONMENT NOVALIDATE "1";
SET ENVIRONMENT NOVALIDATE on;
Similarly, each of the following statements restores the default behavior that requires the explicit NOVALIDATE keyword in SQL operations that create or reset the mode of a constraint without checking: 
SET ENVIRONMENT NOVALIDATE '0';
SET ENVIRONMENT NOVALIDATE OFF;
SET ENVIRONMENT NOVALIDATE "0";
SET ENVIRONMENT NOVALIDATE off;