Sets the default constraint setting for statements.
1 – 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)0[m[1;4mSET[m[1m [1;4mDEFAULT[m[1m [1;4mCONSTRAINT[m[1m MODE qwq> [1;4mIMMEDIATE[m [1mqqqqqqqqqwq> [m
[1mtq> [1;4mDEFAULT[m[1m qqqqqqqqqqqu[m
[1mtq>[m [1;4mDEFERRED[m [1mqqqqqqqqqqu[m
[1m tq> [1;4mON[m [1mqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mOFF[m [1mqqqqqqqqqqqqqqqu [m
[1mmq>[m [1mruntime-options [m [1mqqj[m
(B)0[m[1mruntime-options [m
[1m [m
[1mqqwqqq> 'string-literal' qqqqqqwqqqqq> [m
[1m tqqq> parameter qqqqqqqqqqqqqu [m
[1m mqqq> parameter-marker qqqqqqj [m
3 – Arguments
3.1 – 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.2 – DEFERRED
Synonymous with DEFAULT. However, in a future release of Oracle
Rdb this keyword will change meaning.
3.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.4 – 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.5 – 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.6 – '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.
4 – 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>