|
|
The SET CONSTRAINTS statement sets the UNIQUE, REFERENTIAL or CHECK
constraint error checking mode.
ISQL or Application Programs
SET ConstraintType [,...] CONSTRAINTS {DEFERRED
IMMEDIATE}
- ConstraintType
identifies the type of constraint that is to be affected by the
statement. Each ConstraintType can be one of the
following:
- 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.
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.
Anyone can issue a SET CONSTRAINTS statement.
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
|