SET Database Object Mode statement

Use the SET Database Object Mode statement to change the filtering mode of constraints and of unique indexes, or to enable or disable constraints, indexes, and triggers, or to bypass referential-integrity checking of constraints while this statement is resetting the constraint mode.

This statement is an extension to the ANSI/ISO standard for SQL. To specify whether constraints are checked at the statement level or at the transaction level, see SET Transaction Mode statement.

Syntax

(1)
Notes:

Usage

In the context of this statement, database object has the restricted meaning of an index, a trigger, or a constraint, rather than the more general meaning of this term that the description of the Database Object Name segment defines in Other syntax segments.

The scope of the SET Database Object Mode statement is restricted to constraints, indexes, or triggers in the local database to which the session is currently connected. After you change the mode of an object, the new mode is in effect for all sessions of that database, and persists until another SET Database Object Mode statement changes it again, or until the object is dropped from the database.

Important:
The NOVALIDATE modes to which this statement can reset foreign-key constraints are exceptions to the general statement above, as the following section of this topic explains.

Object modes for triggers, indexes and constraints

Only two object modes are available for triggers and for indexes that allow duplicate values:
  • Enabled (specified by the ENABLED keyword)
  • Disabled (specified by the DISABLED keyword)
For constraints and for unique indexes, you can also specify two additional modes:
  • filtering without integrity-violation errors (by the FILTERING WITHOUT ERROR keywords)
  • filtering with integrity-violation errors (by the FILTERING WITH ERROR keywords)
For foreign-key and check constraints, you can also specify three additional modes:
  • enabled, but without checking for integrity-violation errors (by the ENABLED NOVALIDATE keywords)
  • filtering with integrity-violation errors, but without checking for integrity-violation errors (by the FILTERING WITH ERROR NOVALIDATE keywords)
  • filtering without integrity-violation errors, but without checking for integrity-violation errors (by the FILTERING WITHOUT ERROR NOVALIDATE keywords).
The constraint modes with the NOVALIDATE keyword only persist while the SET Database Object Mode statement is running, after which the constraint mode reverts to the corresponding enabled or filtering mode, and with enforcement of referential integrity or check conditions during subsequent DML operations. But for large tables that are thought to be free of referential or check constraint violations, these modes that bypass validation of the foreign-key or check constraint can significantly reduce the time required to migrate or to import large data sets.

At any given time, an object must be in exactly one of these modes. These modes, which are sometimes called object states, are described in the section Definitions of Database Object Modes.

The sysobjstate system catalog table lists all of the constraint, index, and trigger objects in the database, and the current mode of each object. Because the NOVALIDATE modes persist only during the SET CONSTRAINTS statement or ALTER TABLE ADD CONSTRAINT statement that specified that mode, the sysobjstate table ignores NOVALIDATE modes, which suppress violation-checking only within those DDL statements. For information on the sysobjstate table, see the HCL OneDB™ Guide to SQL: Reference.

In cluster environments, the SET Database Object Mode statement is not supported on updatable secondary servers. (More generally, any session-level index, trigger, or constraint modes that the statement specifies are not redirected for UPDATE operations on table objects in databases of secondary servers.)