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
(B)0[m[1;4mSET[m[1m [1;4mALL[m[1m [1;4mCONSTRAINTS[m[1m qwq> [1;4mIMMEDIATE[m[1m qqwq> [m
[1mtq> [1;4mDEFAULT[m[1m qqqqu[m
[1m tq> [1;4mDEFERRED[m[1m qqqu [m
[1m tq> [1;4mON[m[1m qqqqqqqqqu [m
[1m mq> [1;4mOFF[m[1m qqqqqqqqj [m
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>