SET CONSTRAINTS

The SET CONSTRAINTS statement sets the UNIQUE, REFERENTIAL or CHECK constraint error checking mode.

Scope

ISQL or Application Programs

SQL Syntax

  SET ConstraintType [,...]CONSTRAINTS {DEFERRED 
                                        IMMEDIATE}

Parameters

ConstraintType

identifies the type of constraint that is to be affected by the statement. Each ConstraintType can be one of the following:

UNIQUE
REFERENTIAL
CHECK

DEFERRED

specifies that constraint error violations are not checked until the constraint checking mode is reset to IMMEDIATE, or the current transaction ends.

IMMEDIATE

specifies that constraint errors are checked at the level set by the SET DML ATOMICITY statement, when the SET CONSTRAINTS IMMEDIATE statement successfully executes. This is the default constraint error checking mode.

Description

Setting constraint checking to DEFERRED does not defer checking of non-constraint errors. They are still checked at the current level specified by the SET DML ATOMICITY statement.
When you use SET CONSTRAINTS DEFERRED, error checking for constraint violations is not enabled until you either SET CONSTRAINTS IMMEDIATE or end the transaction with a COMMIT WORK.
You can set the constraint error checking mode to IMMEDIATE at any time in the flow of processing.
When you set constraint checking to IMMEDIATE, and constraint errors currently exist, the SET CONSTRAINTS statement does not succeed. The constraint violations cause an error message to be issued and constraint checking to remain deferred.
You have the option of correcting the error before issuing a COMMIT WORK or allowing the COMMIT WORK statement to be executed.
- If errors remain when you COMMIT WORK, no matter to what level DML atomicity is set, error checking is done at the transaction level and the entire transaction will be rolled back.
- When no constraint errors exist, SET CONSTRAINTS IMMEDIATE succeeds, and error checking thereafter occurs at the level in effect from the SET DML ATOMICITY statement.
If constraint checking is set to DEFERRED and you again set it to DEFERRED, a warning message is issued. If constraint checking is set to IMMEDIATE and you again set it to IMMEDIATE, a warning message is issued.
COMMIT WORK and ROLLBACK WORK statements both reset constraint checking to IMMEDIATE.
The SET CONSTRAINTS statement is sensitive to savepoints. If you establish a save point, then change the constraint checking mode, and then roll back to the savepoint, the constraint mode set after the savepoint will be undone.
When UNIQUE is specified as a ConstraintType, unique indexes are checked for errors also.
HASH unique constraint checking cannot be deferred. Refer to the CREATE TABLE statement for information on HASH unique constraints.
View check constraint checking cannot be deferred.
The SET CONSTRAINTS statement affects only the current session.
The current setting does not appear in the ISQL LIST SET command.

Authorization

Anyone can issue a SET CONSTRAINTS statement.

Example

BEGIN WORK

Constraints are deferred so that the insert and update statements will succeed even though they have unresolved constraint errors. By the end of the transaction, the constraint errors must be resolved or the entire transaction is rolled back.

SET REFERENTIAL CONSTRAINTS DEFERRED

A transaction appears here that contains some insert, update, and delete statements:

   INSERT ...
   UPDATE ...
   DELETE ...
   UPDATE ...
   UPDATE ...

If there are unresolved referential constraints, an error message appears and constraint checking remains in the deferred mode.

SET REFERENTIAL CONSTRAINTS IMMEDIATE

You can correct the constraint errors so you can successfully COMMIT WORK.

If you do not, the COMMIT WORK will roll back the entire transaction because of the remaining violations. Issue error correction statements, here.

Constraint error checking is set to IMMEDIATE by the COMMIT WORK statement or a ROLLBACK WORK statement.

COMMIT WORK