1  SET_ALL_CONSTRAINTS
   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.
 

2  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 --------+

 

2  Arguments
 

3  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  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  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.
 

2  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>