OCP 1Z0 052 169

169. You have two tables with referential integrity enforced between them. You need to insert data to the 
child table first because it is going to be a long transaction and data for the parent table will be available in 
a later stage, which can be inserted as part of the same transaction. 
View the Exhibit to examine the commands used to create tables.  



Which action would you take to delay the referential integrity checking until the end of the transaction?  
A.Set the constraint to deferred before starting the transaction. 
B.Alter the constraint to NOVALIDATE state before starting the transaction. 
C.Enable the resumable mode for the session before starting the transaction. 
D.Set the COMMIT_WAIT parameter to FORCE_WAIT for the session before starting the transaction. 
Answer: A   
http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52217

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

INITIALLY Clause The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE. TheINITIALLY setting can be overridden by a SET CONSTRAINT(S) statement in a subsequent transaction.

• Specify INITIALLY IMMEDIATE to indicate that Oracle should check this constraint at the end of each subsequent SQL statement. If you do not specify INITIALLY at all, then the default is INITIALLY IMMEDIATE.

  If you declare a new constraint INITIALLY IMMEDIATE, then it must be valid at the time the CREATE TABLE or ALTER TABLEstatement is committed or the statement will fail.

• Specify INITIALLY DEFERRED to indicate that Oracle should check this constraint at the end of subsequent transactions.

http://docs.oracle.com/cd/E11882_01/server.112/e25494/general.htm#ADMIN11551

Set All Constraints Deferred

Within the application being used to manipulate the data,you must set all constraints deferred before you actually begin processing any data. Use the following DML statement to set all deferrable constraints deferred:

SET CONSTRAINTS ALL DEFERRED; 

Note:

The SET CONSTRAINTS statement applies only to the current transaction. The defaults specified when you create a constraint remain as long as the constraint exists. The ALTER SESSION SET CONSTRAINTSstatement applies for the current session only.

SQL> CREATE TABLE items  2  (item_code NUMBER(4) CONSTRAINT pk PRIMARY KEY  3  DEFERRABLE INITIALLY IMMEDIATE,  4  item_desc VARCHAR2(40),  5  qoh NUMBER(3),  6  price NUMBER(10,2)  7  );Table createdSQL> CREATE TABLE orders(  2  ord_id CHAR(5) NOT NULL,  3  item_code NUMBER(4) REFERENCES items(item_code)  4  ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,  5  qty NUMBER(3) CONSTRAINT ch_qty CHECK(qty > 0),  6  ord_dt DATE  7  );Table created

SQL> SET CONSTRAINTS ALL DEFERRED;Constraints set

未设置DEFERRABLE属性的约束不能使用此特性，如：

SQL> insert into orders(ord_id,item_code,qty) values('1',2,0);insert into orders(ord_id,item_code,qty) values('1',2,0)ORA-02290: check constraint (TEST.CH_QTY) violated

当提交时，数据不符合约束将回滚

SQL> insert into orders(ord_id,item_code,qty) values('1',2,3);1 row insertedSQL> commit;commitORA-02091: transaction rolled backORA-02291: integrity constraint (TEST.SYS_C0014489) violated - parent key not found

SQL> SET CONSTRAINTS ALL DEFERRED;Constraints setSQL> insert into orders(ord_id,item_code,qty) values('1',2,3);1 row insertedSQL> insert into items(item_code) values(2);1 row insertedSQL> commit;Commit complete