ALLBASE/SQL Reference Manual (36216-90216)

Chapter 12 521
SQL Statements S - Z
SET CONSTRAINTS
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 checkedat 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