SET CONSTRAINTS Mode

The SET CONSTRAINTS statement makes constraints either IMMEDIATE or DEFERRED for an exacting transaction. This statement is used to set the mode for a list of constraint names or for ALL constraints.

A SET CONSTRAINTS mode lasts for the duration of the transaction or until another SET CONSTRAINTS statement resets the mode.

SET CONSTRAINTS ... IMMEDIATE causes the specified constraints to be checked immediately on execution of each constrained statement. Oracle first checks any constraints which were deferred earlier in the transaction and then continues instantly checking constraints of any additional statements in that transaction (as long as all the checked constraints are consistent and no other SET CONSTRAINTS statement is issued by that transaction). If any constraint fails the check an error is signalled; at this point, a COMMIT would cause the overall transaction to roll back.

Example

Create table testconst(x number, y number constraint ck1 check (y > 20) deferrable); Set constraint ck1 deferred;
Will display like:

Constraint set.

When  records  are  inserted  with  violation  checking on that record  is  not  performed  immediately.  The following is descriptive of this:
insert into testconst values(12,11);

1 row created.
commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (HEMA.CK1) violated

Only when a COMMIT takes place the constraint is validated and the overall transaction is rolled back. In addition, the set statement is only valid for one single transaction. Before the second transaction begins the set statement must be given again.