Filtering Modes

A constraint or unique index in a filtering mode can insert into an associated violations table any rows that fail to comply with the constraint or index during DML operation. This mode also supports WITH ERROR and WITHOUT ERROR options for processing referential-integrity violations from INSERT, DELETE, MERGE, and UPDATE statements.

When a constraint or unique index is in FILTERING WITH ERROR mode, the database server returns a referential-integrity violation error message after the INSERT, DELETE, MERGE, or UPDATE statement results in one or more rows that are not in compliance with the unique index or with the constraint.

By default, the FILTERING keyword with no error option specifies the FILTERING WITHOUT ERROR object mode.

Effects of FILTERING mode in DML operations

When a constraint or unique index is in FILTERING WITHOUT ERROR mode, the INSERT, DELETE, MERGE, or UPDATE statement succeeds, but the database server enforces the constraint or the unique-index requirement by writing any failed rows to the violations table associated with the target table. Diagnostic information about the constraint violation or unique-index violation is written to the diagnostics table associated with the target table.

In data manipulation operations, filtering mode has the following specific effects on INSERT, UPDATE, and DELETE statements:
  • A constraint violation during an INSERT statement causes the database server to make a copy of the nonconforming record and write it to the violations table. The database server does not write the nonconforming record to the target table.

    If the INSERT statement is not a singleton INSERT, the rest of the insert operation proceeds with the next record.

  • A constraint violation or unique-index violation during an UPDATE statement causes the database server to make a copy of the existing record that was to be updated and write it to the violations table. The database server also makes a copy of the new record and writes it to the violations table, but the actual record is not updated in the target table. If the UPDATE statement is not a singleton update, the rest of the update operation proceeds with the next record.
  • A constraint violation or unique-index violation during a DELETE statement causes the database server to make a copy of the record that was to be deleted and write it to the violations table. The database server does not delete the actual record in the target table. If the DELETE statement is not a singleton delete, the rest of the delete operation proceeds with the next record.
  • In MERGE statements, the component INSERT, DELETE, or UPDATE operations are processed as respectively described above.

In all of these cases, the database server sends diagnostic information about each constraint violation or unique-index violation to the diagnostics table associated with the target table.

For information on the structure of the records that the database server writes to the violations and diagnostics tables, see Structure of the violations table and Structure of the diagnostics table.

FILTERING NOVALIDATE modes

While the SET Database Object Mode statement is changing the mode of a foreign-key or check constraint to FILTERING WITHOUT ERROR or to FILTERING WITH ERROR, the database server validates the constraint by examining every row in the constrained table. For large tables, this validation can require significant time and resources.

You can instead bypass the search for violating rows during the SET Database Object mode operation by including the NOVALIDATE keyword to change the constraint mode to FILTERING WITHOUT ERROR NOVALIDATE or to FILTERING WITH ERROR NOVALIDATE, as in these examples for foreign-key constraints: 
SET CONSTRAINTS (refcon_1, refcon_2) FILTERING WITH ERROR;
SET CONSTRAINTS (refcon_3, refcon_4) FILTERING WITHOUT ERROR;

For constraints on large tables that need to be relocated, specifying NOVALIDATE for ENABLED or FILTERING modes can substantially reduce the time required to change the mode of the constraint to a filtering mode.

After the SET Database Object Mode statement successfully enables a constraint in a NOVALIDATE filtering mode, the constraint is registered in FILTERING WITHOUT ERROR mode (F) or in FILTERING WITH ERROR mode (G) in the sysobjstate system catalog table. Because the NOVALIDATE keyword has no encoding in the system catalog, it has no subsequent effect on the behavior of the database server. The database server enforces the foreign-key constraint during subsequent DML operations as the SET CONSTRAINTS statement specified, with or without integrity violation errors, to maintain the referential integrity of the database.

Important:

When the ALTER TABLE ADD CONSTRAINT statement defines an ENABLED referential constraint in NOVALIDATE mode on a table that contains data, the database server can achieve the same efficiencies of bypassing constraint validation that are described above for the SET CONSTRAINTS statement. For more information about the NOVALIDATE option in ALTER TABLE operations that define new referential constraints, see Creating foreign-key constraints in NOVALIDATE modes.