1  SET_DEFAULT_CONSTRAINT_MODE
   Sets the default constraint setting for statements.
 

2  Environment
   You can use the SET DEFAULT CONSTRAINT MODE statement:

   o  In interactive SQL

   o  In Dynamic SQL as a statement to be dynamically executed
 

2  Format

 (B)0SET DEFAULT CONSTRAINT MODE qwq> IMMEDIATE qqqqqqqqqwq>  
                              tq> DEFAULT qqqqqqqqqqqu
                              tq> DEFERRED qqqqqqqqqqu
                              tq> ON qqqqqqqqqqqqqqqqu    
                              tq> OFF qqqqqqqqqqqqqqqu    
                              mq> runtime-options  qqj



 (B)0runtime-options                        
                                        
 qqwqqq> 'string-literal' qqqqqqwqqqqq> 
   tqqq> parameter qqqqqqqqqqqqqu       
   mqqq> parameter-marker qqqqqqj       

 

2  Arguments
 

3  DEFAULT



   Syntax:

   DEFAULT | OFF

   Requests that during the next transaction, all constraints
   defined as DEFERRABLE INITIALLY DEFERRED be evaluated as
   originally specified in the constraint definition. OFF is
   synonymous with DEFAULT.
 

3  DEFERRED

   Synonymous with DEFAULT. However, in a future release of Oracle
   Rdb this keyword will change meaning.
 

3  IMMEDIATE



   Syntax:

   IMMEDIATE | ON

   This option requests that during this transaction, all
   constraints defined as DEFERRABLE INITIALLY DEFERRED be evaluated
   as though defined as DEFERRABLE INITIALLY IMMEDIATE. ON is
   synonymous with IMMEDIATE.
 

3  parameter

   Specifies the default character set for your session. The value
   of runtime-options must be a valid character set. For a list of
   allowable character set names and option values, see Oracle Rdb
   SQL Reference Manual.
 

3  parameter-marker

   Specifies the default character set for your session. The value
   of runtime-options must be a valid character set. For a list of
   allowable character set names and option values, see Oracle Rdb
   SQL Reference Manual.
 

3  'string-literal'

   Specifies the default character set for your session. The value
   of runtime-options must be a valid character set. For a list of
   allowable character set names and option values, see Oracle Rdb
   SQL Reference Manual.
 

2  Examples

   Example 1: Using the SET statement to change the current setting
   for constraint evaluation

   The following example shows how to use the SET statement
   to change the constraint evaluation mode for the current
   transaction. You can display both the current setting and the
   default setting.

   SQL> attach 'filename mf_personnel_sql';
   SQL> /*
   ***> Show settings before starting, set the default mode,
   ***> then show the settings again.
   ***> */
   SQL> show constraint mode;
       Statement constraint evaluation default is DEFERRED (off)
   SQL> set default constraint mode immediate;
   SQL> show constraint mode;
       Statement constraint evaluation default is IMMEDIATE (on)
   SQL> start transaction;
   SQL> set all constraints deferred;
   SQL> show constraint mode;
       Statement constraint evaluation default is IMMEDIATE (on)
       Statement constraint evaluation is DEFERRED (off)
   SQL> commit;
   SQL> show constraint mode;
       Statement constraint evaluation default is IMMEDIATE (on)
   SQL>

   Example 2: Using runtime options

   If using runtime-options the passed character value must be one
   of the keywords: ON, OFF, IMMEDIATE, DEFERRED, or DEFAULT. The
   following example shows how this can be done in Interactive SQL.

   SQL> show constraint mode
       Statement constraint evaluation default is DEFERRED (off)
   SQL> declare :c_mode char(10) = 'IMMEDIATE';
   SQL> set default constraint mode :c_mode;
   SQL> show constraint mode
       Statement constraint evaluation default is IMMEDIATE (on)
   SQL>