| Informix Guide to SQL: Syntax SQL Statements |
|
|
Use the SET Transaction Mode statement to specify whether constraints are checked at the statement level or at the transaction level.
To change the mode of constraints to on, off, or filtering, see SET Database Object Mode. 
|
When you set the transaction mode of a constraint, the effect of the SET Transaction Mode statement is limited to the transaction in which it is executed. The setting that the SET Transaction Mode statement produces is effective only during the transaction.
You use the IMMEDIATE keyword to set the transaction mode of constraints to statement-level checking. You use the DEFERRED keyword to set the transaction mode to transaction-level checking.
You can set the transaction mode of constraints only in a database with logging.
When you set the transaction mode to immediate, statement-level checking is turned on, and all specified constraints are checked at the end of each INSERT, UPDATE, or DELETE statement. If a constraint violation occurs, the statement is not executed. Immediate is the default transaction mode of constraints.
When you set the transaction mode of constraints to deferred, statement-level checking is turned off, and all specified constraints are not checked until the transaction is committed. If a constraint violation occurs while the transaction is being committed, the transaction is rolled back.
Tip: If you defer checking a primary-key constraint, checking the not-null constraint for that column or set of columns is also deferred.
Duration of Transaction Modes
The duration of the transaction mode that the SET Transaction Mode statement specifies is the transaction in which the SET Transaction Mode statement is executed. You cannot execute this statement outside a transaction. Once a COMMIT WORK or ROLLBACK WORK statement is successfully completed, the transaction mode of all constraints reverts to IMMEDIATE.
To switch from transaction-level checking to statement-level checking, you can use the SET Transaction Mode statement to set the transaction mode to immediate, or you can use a COMMIT WORK or ROLLBACK WORK statement in your transaction.
You can specify all constraints in the database in your SET Transaction Mode statement, or you can specify a single constraint or list of constraints.
If you specify the ALL keyword, the SET Transaction Mode statement sets the transaction mode for all constraints in the database. If any statement in the transaction requires that any constraint on any table in the database be checked, the database server performs the checks at the statement level or the transaction level, depending on the setting that you specify in the SET Transaction Mode statement.
If you specify a single constraint name or a list of constraints, the SET Transaction Mode statement sets the transaction mode for the specified constraints only. If any statement in the transaction requires checking of a constraint that you did not specify in the SET Transaction Mode statement, that constraint is checked at the statement level regardless of the setting that you specified in the SET Transaction Mode statement for other constraints.
When you specify a list of constraints, the constraints do not have to be defined on the same table, but they must exist in the same database.
You can set the transaction mode of local constraints or remote constraints. That is, the constraints that are specified in the SET Transaction Mode statement can be constraints that are defined on local tables or constraints that are defined on remote tables.
The following example shows how to defer checking constraints within a transaction until the transaction is complete. The SET Transaction Mode statement in the example specifies that any constraints on any tables in the database are not checked until the COMMIT WORK statement is encountered.
The following example specifies that a list of constraints is not checked until the transaction is complete:
Related Statements: ALTER TABLE and CREATE TABLE