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 the 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 TheDEFERRABLE and NOTDEFERRABLE parameters indicate whether or not, insubsequent transactions, constraint checking can be deferred untilthe end of the transaction using the SETCONSTRAINT(S) statement. If you omit thisclause, then the default is NOTDEFERRABLE.

• Specify NOTDEFERRABLE to indicate that in subsequent transactionsyou cannot use the SETCONSTRAINT[S] clause to defer checking ofthis constraint until the transaction is committed. The checking ofa NOT 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 theCREATE TABLE or ALTERTABLE 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

• "DEFERRABLE Constraint Examples"

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

INITIALLYClause The 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 constraintINITIALLY IMMEDIATE, then it must bevalid at the time the CREATE TABLE orALTER TABLE statement is committed or thestatement will fail.

• SpecifyINITIALLY 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 NOTDEFERRABLE, because a NOTDEFERRABLE 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]

Purpose

Usethe SET 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 a SET CONSTRAINTS 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 be NOT DEFERRABLE.

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