oracle 约束：延迟 与 立即

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#i1002273

 

constraint_state::=

Description of the illustrationconstraint_state.gif

 

constraint_state Youcan use theconstraint_state with bothinline and out-of-line specification. You can specify the clausesofconstraint_state in any order,but you can specify each clause only once.

DEFERRABLEClause TheDEFERRABLE andNOTDEFERRABLE parameters indicate whether or not, insubsequent transactions, constraint checking can be deferred untilthe end of the transaction using theSETCONSTRAINT(S) statement. If you omit thisclause, then the default isNOTDEFERRABLE.

• Specify NOTDEFERRABLE to indicate that in subsequent transactionsyou cannot use theSETCONSTRAINT[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 NOTDEFERRABLE, then it must be valid at the time theCREATETABLE or ALTERTABLE statement is committed or the statement willfail.

•  

• Specify DEFERRABLE toindicate that in subsequent transactions you can use theSETCONSTRAINT[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 theconstraintNOT DEFERRABLE implicitly byspecifying neither of them, you cannot specify this clause in anALTERTABLE 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 andOracle Database Concepts for more informationabout deferred constraints

• "DEFERRABLE Constraint Examples"

Restriction on[NOT] DEFERRABLE You cannot specify eitherof these parameters for a view constraint.

INITIALLYClause TheINITIALLY clause establishes the defaultchecking behavior for constraints that areDEFERRABLE.The INITIALLY setting can be overridden by aSETCONSTRAINT(S) statementin a subsequent transaction.

• SpecifyINITIALLY 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 isINITIALLYIMMEDIATE.

  If you declare a new constraintINITIALLYIMMEDIATE, then it must bevalid at the time the CREATE TABLE orALTER TABLE statement is committed or thestatement will fail.

• SpecifyINITIALLYDEFERRED to indicate thatOracle should check this constraint at the end of subsequenttransactions.

This clause is not valid if you havedeclared the constraint to beNOTDEFERRABLE, because a NOTDEFERRABLE constraint is automaticallyINITIALLYIMMEDIATE and cannot ever beINITIALLY DEFERRED.

 

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10003.htm#SQLRF01703

SETCONSTRAINT[S]

Purpose

UsetheSET CONSTRAINTS statement to specify,for a particular transaction, whether a deferrable constraint ischecked following each DML statement or when the transaction iscommitted.

Prerequisites

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.

Syntax

set_constraints::=

Description of the illustrationset_constraints.gif

Semantics

 

constraint

Specify the name of one or more integrityconstraints.

 

ALL

Specify ALL to set all deferrable constraints forthis transaction.

 

IMMEDIATE

Specify IMMEDIATE to indicate that theconditions specified by the deferrable constraint are checkedimmediately after each DML statement.

 

DEFERRED

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 aSETCONSTRAINTS ALL IMMEDIATEstatement.

Examples

Setting Constraints:Examples The following statement sets alldeferrable constraints in this transaction to be checkedimmediately following each DML statement:

SET CONSTRAINTS ALL IMMEDIATE;

The following statement checks three deferred constraints whenthe transaction is committed. This example fails if the constraintswere specified to beNOT DEFERRABLE.

SET CONSTRAINTS emp_job_nn, emp_salary_min ,   hr.jhist_dept_fk@remote DEFERRED;