SET CONSTRAINTS statement

Use the SET CONSTRAINTS statements to change how some or all of the existing constraints on a table are processed.

Syntax

Only the CREATE TABLE, CREATE TEMP TABLE and ALTER TABLE ADD CONSTRAINT statements of SQL can create new constraints. The SET CONSTRAINTS statement supports the following syntax for modifying how the database server enforces (or ignores) one or more existing constraints on a single table:

(explicit id secon202) secon202 SET CONSTRAINTS { { { constraint | ALL } { IMMEDIATE | DEFERRED } | [] { constraint | FOR [ 'owner' . ] table } <Constraint Mode> } }

Constraint Mode

{ DISABLED | { ENABLED | FILTERING { WITHOUT ERROR | WITH ERROR } (explicit id ) } [ (explicit id ) NOVALIDATE ] }
Element Description Restrictions Syntax
constraint Constraint whose mode is to be reset Must exist, and must all be defined on the same table Identifier
owner Owner of table Must own table Owner name
table Table whose constraint mode is to be reset for all constraints Must exist in the database Identifier

Usage

Constraint-mode keyword options of the SET CONSTRAINTS statements include these:
  • Whether constraints are checked at the statement level (IMMEDIATE) or at the transaction level (DEFERRED)
  • Whether to enable (ENABLED) or disable (DISABLED) constraints
  • Whether the filtering mode of constraints on tables with violations tables should be FILTERING WITH ERROR or FILTERING WITHOUT ERROR
  • Whether to enable referential constraints without verifying (NOVALIDATE) that the foreign-key value in every row matches a primary-key value in the referenced table.
  • Whether to enable check constraints without verifying (NOVALIDATE) that the value in every row satisfies the specified conditions.

The SET CONSTRAINTS keywords can begin the SET Transaction Mode statement, which is described in SET Transaction Mode statement.

The SET CONSTRAINTS keywords can also begin a special case of the SET Database Object Mode statement, which is an extension to the ANSI/ISO standard for SQL. Besides constraints, the SET Database Object Mode statement can also enable or disable a trigger or index, or change the filtering mode of a unique index. For the complete syntax and semantics of that statement, see SET Database Object Mode statement.

For information on using the SET CONSTRAINTS statement to enable or disable system-defined indexes that are implicitly created by PRIMARY KEY and FOREIGN KEY constraint definitions, see the topic SET INDEXES statement.

Persistence of Constraint Modes

Any changes that you make to the mode of a constraint persist until that constraint mode setting is modified again, or until that constraint or its table are dropped.

The NOVALIDATE modes, however, are exceptions, because these mode do not persist beyond the SET CONSTRAINTS statement (or beyond the ALTER TABLE ADD CONSTRAINT statement) that specified the NOVALIDATE mode.

For example, for foreign-key constraints, when the DDL statement that specifies a NOVALIDATE mode completes, the constraint mode reverts to whichever mode the sysobjstate system catalog table recorded for that foreign-key constraint among these three possible modes:
  • ENABLED NOVALIDATE becomes ENABLED
  • FILTERING WITH ERROR NOVALIDATE becomes FILTERING WITH ERROR
  • FILTERING WITHOUT ERROR NOVALIDATE becomes FILTERING WITHOUT ERROR.
In all subsequent DML operations on the table, such as DELETE, INSERT, MERGE, or UPDATE statements of SQL, the database server enforces the enabled foreign-key constraint at a time determined by its IMMEDIATE or DEFERRED setting, but ignoring any previous NOVALIDATE mode.

Restrictions on Secondary Servers

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

1 Informix® extension. Constraint must be on a table in the current database.
2 See Filtering Modes
3 Valid for FOREIGN KEY or CHECK constraints only