Oracle SQL: the Essential Reference

SET CONSTRAINT

SET {CONSTRAINT | CONSTRAINTS} {ALL | constraint[,constraint...]} {IMMEDIATE | DEFERRED}

Specifies at the transaction level whether specific constraints are checked after each DML statement or deferred until the end of a transaction.

This statement applies only to deferrable constraints.

Keywords

ALL

Specifies that all deferrable constraints for the transaction are affected by this statement.

constraint

Specifies the name of a deferrable constraint.

IMMEDIATE

Specifies that the conditions enforced by the constraints are to be checked after each DML statement is completed.

DEFERRED

Specifies that the conditions enforced by the constraints are to be checked after the entire transaction is complete and committed.

The success of deferrable constraints can be tested by issuing the statement SET CONSTRAINTS ALL IMMEDIATE before the COMMIT statement is issued.

Example

Defer two constraints until the transaction is complete:

SET CONSTRAINTS chk_sal,chk_comm DEFERRED;