With the SET CONSTRAINTS statement you can defer constraint error checking for any constraint type for all or part of a transaction.
You can defer constraint error checking at any time, within or outside of a transaction. Note, however, that both error checking and constraint error checking are set to their defaults when a transaction ends.
Error checking is set to row level, and constraint error checking is set to immediate when a transaction is committed or rolled back.
Within a transaction, the SET CONSTRAINTS statement does not succeed if you attempt to set deferred constraint checking
to immediate when constraint errors exist. An error message is
generated indicating that there are one or more constraint errors of a
particular constraint type. Constraint checking remains deferred.
If there are unresolved constraint errors, you can check error codes
and choose to correct the the errors and issue a COMMIT WORK statement,
or you can issue a ROLLBACK WORK statement.
You might want to defer constraint checking to avoid errors on referential constraints that will be resolved by the end of a transaction. Refer to the "Coding with Deferred Constraint Error Checking" section later in this chapter for an example.
Another use might be to avoid a partially processed SQL statement
(due to constraint errors) within a transaction in which row level error checking is in effect. You can defer constraint checking until just before the end of the transaction. Then set constraints to immediate to determine if any constraint errors occurred. If errors were detected, you can correct them before ending the transaction. Refer to the ISQL LOAD command for an example of loading data with row level error checking and constraint checking deferred.
Refer to the "SQL Commands" chapter of the ALLBASE/SQL Reference Manual for the full syntax of the SET CONSTRAINTS statement.
