HP 3000 Manuals HP 3000 Manuals
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