Modes for constraints and unique indexes

You can specify enabled or disabled mode for a constraint or for a unique index. For tables that the START VIOLATIONS TABLE statement has associated with a violations table and a diagnostics table, you can also use the FILTERING keyword to specify an ERROR mode for processing rows that do not comply with constraints or with unique index requirements.

When you change the mode of a foreign-key constraint to ENABLED or to FILTERING, you can optionally include the NOVALIDATE keyword. This suspends referential-integrity checking for rows that violate the constraint during execution of the SET CONSTRAINTS statement.

This is the syntax for changing the database object mode of constraints or of unique indexes in SET CONSTRAINTS or SET INDEXES statements:

Modes for constraints and unique indexes

{ DISABLED | { ENABLED | FILTERING { WITHOUT ERROR | WITH ERROR } (explicit id ) } [ (explicit id ) NOVALIDATE ] }

Usage

If you specify no mode in the ALTER TABLE or CREATE TABLE statement that creates a constraint, the constraint is enabled by default.

Similarly, if you specify no mode in the CREATE INDEX statement that creates an index, the index is enabled by default.

There is no default mode, however, for database objects in SET Database Object Mode statements. If you specify no mode in the SET CONSTRAINTS or the SET INDEXES options of SET Database Object Mode statements, the statement fails with error -201, and the constraint mode or index mode is unchanged.

The WITHOUT ERROR and WITH ERROR filtering options support DML operations in which the database server tests whether new or modified rows violate constraints or unique indexes on the target table. How the database server processes noncompliant rows in filtering mode also depends on these factors:
  • Whether a violations table and a diagnostics table are associated with the table on which the constraint or the unique index is defined.
  • Whether input to the associated violations and diagnostics tables is currently enabled or disabled.
For more information, see the topics START VIOLATIONS TABLE statement and STOP VIOLATIONS TABLE statement.

Examples of changing constraint modes and unique index modes

The following statement disables the constraint u100_1, so that it is still registered in the system catalog, but has no effect:
SET CONSTRAINTS u100_1 DISABLED;
If u100_1 is an enabled unique index, rather than a constraint, then the following statement has a similar effect:
SET INDEXES u100_1 DISABLED;
The following statement enables the referential constraint u100_1 without validating the foreign-key relationships for each row:
SET CONSTRAINTS u100_2 ENABLED NOVALIDATE;
Warning:
You can specify the new mode of a foreign key constraint as ENABLED NOVALIDATE, or as FILTERING WITH ERROR NOVALIDATE or FILTERING WITHOUT ERROR NOVALIDATE. This can improve performance in load operations, for example, if the data set is known to have a corresponding primary key for every row that is in scope of the foreign key constraint. It is the responsibility of the user, however, to avoid corruption of the database in subsequent DML operations. If you are not sure that the data rows are compliant,
  • you should disable the foreign-key constraint,
  • load the data into the new database,
  • and then enable the foreign-key constrain after its table has been successfully loaded, so that the database server can validate the referential integrity of the data.
The database server automatically drops the NOVALIDATE attribute when the SET CONSTRAINTS statement completes execution. The following statement enables the same foreign-key constraint and restores automatic validation of the constraint:
SET CONSTRAINTS u100_2 ENABLE;

When you use the FILTERING WITHOUT ERROR keywords to define a filtering mode, subsequent violations of that constraint, or uniqueness violations of that index, do not cause INSERT, DELETE, MERGE, or UPDATE operations to fail if some rows violate the constraint or the unique index. In this filtering mode, the DML statement succeeds, but the database server enforces the constraint or the unique index requirement by writing the noncompliant rows to the violations table.

The following statement instructs the database server to write any rows that violate the r104_11 constraint to the violations table, provided that a violations table is associated with the target table.
SET CONSTRAINTS r104_11 FILTERING WITHOUT ERROR;
For more information about filtering modes, see the topic Filtering Modes.
The following statement disables all constraints defined on the orders table:
SET CONSTRAINTS FOR orders DISABLED;
Subsequent DML operations on that table ignore rows that violate constraints on the orders table, creating no entries in its violations or diagnostics tables, if those tables exist. If any unique indexes exist on the orders table, however, rows that violate uniqueness requirements are processed according to the current modes of the indexes, as listed in the sysobjstate system catalog table.
1 See Filtering Modes
2 Valid for FOREIGN KEY and CHECK constraints only