Choosing a Constraint-Mode Option

Use the constraint-mode options (ENABLED, DISABLED, and FILTERING) to control the behavior of constraints in INSERT, DELETE, MERGE, and UPDATE operations.

For constraints that the CREATE TABLE statement defines, these are the options.

DISABLED: Does not enforce the constraint during INSERT, DELETE, and UPDATE operations
ENABLED: Enforces the constraint during INSERT, DELETE, and UPDATE operations If a target row causes a violation of the constraint, the statement fails. This mode is the default.
FILTERING: Enforces the constraint during INSERT, DELETE, and UPDATE operations, if the START VIOLATIONS statement has created a violations table and a diagnostics table. If a target row causes a violation of the constraint, the statement continues processing. The database server writes the row in question to the violations table associated with the target table, and writes diagnostic information to the associated diagnostics table.

If you choose filtering mode, you can specify the WITHOUT ERROR or WITH ERROR options. The following list explains these ERROR options.

WITH ERROR: Returns an integrity-violation error when a filtering-mode constraint is violated during an INSERT, DELETE, or UPDATE operation.
WITHOUT ERROR: Does not return an integrity-violation error when a filtering-mode constraint is violated during an INSERT, DELETE, or UPDATE operation. This is the default ERROR option.

Note:

For the FILTERING WITHOUT ERROR mode to have these effects, you must also use the START VIOLATIONS TABLE statement to start the violations and diagnostics tables for the target table on which the constraints are defined. You can issue that statement

either before you set any constraints on the table to a filtering mode,
or after you set constraints to a filtering mode, but before any users perform INSERT, DELETE, or UPDATE operations on rows in the table.

Constraint modes are registered in the sysobjstate system catalog table.

NOVALIDATE modes for foreign-key or check constraints

The modes listed above are only a subset of the constraint modes that the SET CONSTRAINTS option to the SET Database Object Mode statement can specify while it is resetting the mode of an existing foreign-key or check constraint. They are also a subset of the constraint modes that the ALTER TABLE ADD CONSTRAINT statement can specify while creating a new foreign-key or check constraint on an existing table.

The ALTER TABLE ADD CONSTRAINT and SET CONSTRAINTS statements can specify one of these additional constraint modes by including the NOVALIDATE keyword in the constraint definition. The effect is that the database server skips the checking of existing rows for violations when the constraint is being created or enabled, thereby reducing the time and resources required for processing the DDL statement. When that statement completes execution, however, each NOVALIDATE mode automatically reverts to an ENABLED or FILTERING mode. Thus, the NOVALIDATE keyword does not prevent enforcement of referential integrity or check conditions during subsequent DML operations on the table, because the NOVALIDATE modes do not persist beyond the DDL statement that defined them.

Because most tables are empty when they are created, referential-integrity checking of existing rows typically does not occur during table creation, and the CREATE TABLE statement does not support NOVALIDATE constraint modes. Those modes can be efficient, however, in contexts where non-empty tables with foreign-key or check constraints need to be moved to another database or to a data warehouse.