ALLBASE/SQL Reference Manual (36216-90216)

522 Chapter12
SQL Statements S - Z
SET CONSTRAINTS
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.