文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#i1002273
Description of the illustrationconstraint_state.gif
constraint_state constraint_state
with bothinline and out-of-line specification. You can specify the clausesof constraint_state
in any order,but you can specify each clause only once.
DEFERRABLEClause DEFERRABLE
and NOT
DEFERRABLE
parameters indicate whether or not, insubsequent transactions, constraint checking can be deferred untilthe end of the transaction using the SET
CONSTRAINT
(S
) statement. If you omit thisclause, then the default is NOT
DEFERRABLE
.
-
Specify
NOT
DEFERRABLE
to indicate that in subsequent transactionsyou cannot use theSET
CONSTRAINT
[S
] clause to defer checking ofthis constraint until the transaction is committed. The checking ofaNOT
DEFERRABLE
constraint can never bedeferred to the end of the transaction. -
-
If you declare a new constraint
NOT
DEFERRABLE
, then it must be valid at the time theCREATE
TABLE
orALTER
TABLE
statement is committed or the statement willfail. -
-
Specify
DEFERRABLE
toindicate that in subsequent transactions you can use theSET
CONSTRAINT
[S
] clause todefer checking of this constraint until after the transaction iscommitted. This setting in effect lets you disable the constrainttemporarily while making changes to the database that might violatethe constraint until all the changes arecomplete.
You cannot alter the deferrability of a constraint. That is,whether you specify either of these parameters, or make theconstraint NOT
DEFERRABLE
implicitly byspecifying neither of them, you cannot specify this clause in anALTER
TABLE
statement. You must drop theconstraint and re-create it.
See Also:
-
SET CONSTRAINT[S] for information on setting constraintchecking for a transaction
-
Oracle Database Administrator's Guide and Oracle Database Concepts for more informationabout deferred constraints
Restriction on[NOT] DEFERRABLE
INITIALLYClause INITIALLY
clause establishes the defaultchecking behavior for constraints that are DEFERRABLE
.The INITIALLY
setting can be overridden by aSET
CONSTRAINT
(S
) statementin a subsequent transaction.
-
Specify
INITIALLY
IMMEDIATE
toindicate that Oracle should check this constraint at the end ofeach subsequent SQL statement. If you do not specifyINITIALLY
at all, then the default isINITIALLY
IMMEDIATE
.If you declare a new constraint
INITIALLY
IMMEDIATE
, then it must bevalid at the time theCREATE
TABLE
orALTER
TABLE
statement is committed or thestatement will fail. -
Specify
INITIALLY
DEFERRED
to indicate thatOracle should check this constraint at the end of subsequenttransactions.
This clause is not valid if you havedeclared the constraint to be NOT
DEFERRABLE
, because a NOT
DEFERRABLE
constraint is automaticallyINITIALLY
IMMEDIATE
and cannot ever beINITIALLY
DEFERRED
.
文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10003.htm#SQLRF01703
SETCONSTRAINT[S]
Usethe SET
CONSTRAINTS
statement to specify,for a particular transaction, whether a deferrable constraint ischecked following each DML statement or when the transaction iscommitted.
To specify when a deferrable constraint is checked, you musthave SELECT
privilege on the table to which theconstraint is applied unless the table is in your schema.
Description of the illustrationset_constraints.gif
Specify the name of one or more integrityconstraints.
Specify ALL
to set all deferrable constraints forthis transaction.
Specify IMMEDIATE
to indicate that theconditions specified by the deferrable constraint are checkedimmediately after each DML statement.
Specify DEFERRED
to indicate that theconditions specified by the deferrable constraint are checked whenthe transaction is committed.
Note:
You can verify the success of deferrable constraints prior tocommitting them by issuing aSET
CONSTRAINTS
ALL
IMMEDIATE
statement.
Setting Constraints:Examples
SET CONSTRAINTS ALL IMMEDIATE;
The following statement checks three deferred constraints whenthe transaction is committed. This example fails if the constraintswere specified to be NOT
DEFERRABLE
.
SET CONSTRAINTS emp_job_nn, emp_salary_min , hr.jhist_dept_fk@remote DEFERRED;