Controls checking for constraints that are evaluated at commit
    time. (This statement has no effect on constraints that are
    evaluated at verb time. For verb-time evaluation information,
    see the SET_TRANSACTION.) The SET ALL CONSTRAINTS statement is
    used to evaluate deferrable constraints at intervals before the
    transaction is committed.

1  –  Environment

    You can use the SET ALL CONSTRAINTS statement:

    o  In interactive SQL

    o  Embedded in host language programs to be precompiled

    o  As part of a procedure in an SQL module

    o  In dynamic SQL as a statement to be dynamically executed

2  –  Format

  SET ALL CONSTRAINTS -+-> IMMEDIATE --+->
                       +-> DEFAULT ----+
                       +-> DEFERRED ---+
                       +-> ON ---------+
                       +-> OFF --------+

3  –  Arguments

3.1  –  DEFAULT

    The default constraint mode setting for a session is DEFERRED
    unless you have used one of the following to specify otherwise:

    o  SET DEFAULT CONSTRAINT MODE IMMEDIATE statement

    o  SQLOPTIONS=(CONSTRAINTS=IMMEDIATE) qualifier on the SQL
       precompiler command line

    o  CONSTRAINTS=IMMEDIATE qualifier on the SQL module language
       command line

3.2  –  constraint-evaluation-status

    Syntax options:

    OFF | DEFERRED

    This option causes constraint evaluation to be deferred until
    commit time, when the transaction completes. OFF is synonymous
    with DEFERRED.

3.3  –  IMMEDIATE

    Syntax:

    IMMEDIATE | ON

    This option causes constraint evaluation to be executed
    immediately, when the statement completes. ON is synonymous with
    IMMEDIATE.

    When you issue a SET ALL CONSTRAINTS IMMEDIATE statement, SQL:

    o  Evaluates all previously deferred constraints (those that
       would otherwise be evaluated at a COMMIT statement)

    o  Sets a mode in which SQL evaluates any constraints selected
       for deferred evaluation by the execution of an SQL statement
       at the end of that SQL statement (instead of waiting for a
       COMMIT statement)

    Once the transaction completes, the constraint mode is set back
    to the default constraint mode for subsequent statements.

4  –  Example

    Example 1: Using the SET ALL CONSTRAINTS statement in interactive
    SQL

    SQL> att 'file mf_personnel_sql';
    SQL> set all constraints immediate;
    SQL> show constraint;
        Statement constraint evaluation default is DEFERRED (off)
        Statement constraint evaluation is IMMEDIATE (on)
    SQL> /*
    ***> Show the constraints
    ***> */
    SQL> show tables (constraints) job_history;
    Information for table JOB_HISTORY

    Table constraints for JOB_HISTORY:
    JOB_HISTORY_FOREIGN1
     Foreign Key constraint
     Column constraint for JOB_HISTORY.EMPLOYEE_ID
     Evaluated on COMMIT
     Source:
     JOB_HISTORY.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID)

    JOB_HISTORY_FOREIGN2
     Foreign Key constraint
     Column constraint for JOB_HISTORY.JOB_CODE
     Evaluated on COMMIT
     Source:
     JOB_HISTORY.JOB_CODE REFERENCES JOBS (JOB_CODE)

    JOB_HISTORY_FOREIGN3
     Foreign Key constraint
     Column constraint for JOB_HISTORY.DEPARTMENT_CODE
     Evaluated on COMMIT
     Source:
     JOB_HISTORY.DEPARTMENT_CODE REFERENCES DEPARTMENTS (DEPARTMENT_CODE)

    Constraints referencing table JOB_HISTORY:
    No constraints found

    SQL> set all constraints deferred;
    SQL> show constraint;
    Statement constraint evaluation default is DEFERRED (off)
    Statement constraint evaluation is DEFERRED (off)
    SQL>