OCP-047约束延迟 SET CONSTRAINTS DEFERRABLE OR IMMEDIATE

23. View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE
INITIALLY DEFERRED.
INSERT INTO cust VALUES (1,'RAJ'). –row 1
INSERT INTO cust VALUES (1,'SAM'). –row 2
COMMIT.
SET CONSTRAINT cust_id_pk IMMEDIATE.
INSERT INTO cust VALUES (1,'LATA'). –row 3
INSERT INTO cust VALUES (2,'KING'). –row 4
COMMIT.
Which rows would be made permanent in the CUST table?
A. row 4 only
B. rows 2 and 4
C. rows 3 and 4

D. rows 1 and 4

Answer:C

官方文档解释，约束延迟检查；

当指定约束的属性为延迟时，系统检查在事物完成后；如果是immediate，则是在每条语句执行后检查约束。

Deferred Constraint Checking

You can defer checking constraints for validity until the end of the transaction.

• A constraint is deferred if thesystem checks that it is satisfied only on commit. If a deferred constraint is violated, then commit causes the transaction to undo.

• If a constraint is immediate (not deferred), then it is checked at the end of each statement. If it is violated, the statement is rolled back immediately.

If a constraint causes an action (for example, delete cascade), that action is always taken as part of the statement that caused it, whether the constraint is deferred or immediate.

eg:

SQL> alter table cust add constraint cust_id_PK primary key(cust_id) deferrable initially deferred; 
Table altered 
SQL> insert into cust values(1,'RAJ'); 
1 row inserted 
SQL> insert into cust values(1,'Sam'); 
1 row inserted 
SQL> commit; 
commit
 --------------------------------------------------------------------->在事物commit的时候才检查约束
ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (TEST.CUST_ID_PK) 
SQL> select * from cust; 
   CUST_ID CUST_NAME
---------- --------------------
 
SQL> set constraint cust_id_pk immediate; 
Constraints set 
SQL> insert into cust values(1,'LATA'); 
1 row inserted

SQL> insert into cust values(1,'SAM'); 
insert into cust values(1,'SAM') 
ORA-00001: 违反唯一约束条件 (TEST.CUST_ID_PK)    -------------------------语句结束后检查约束 
SQL> insert into cust values(2,'King');
 1 row inserted 
SQL> commit; 
Commit complete

SQL> select * from cust; 
   CUST_ID CUST_NAME
---------- --------------------
         1 LATA
         2 King

SET CONSTRAINTS Mode

The SET CONSTRAINTS statement makes constraints either DEFERRED or IMMEDIATE for a particular transaction (following the ANSI SQL92 standards in both syntax and semantics). You can use this statement to set the mode for a list of constraint names or for ALL constraints.

The SET CONSTRAINTS mode lasts for the duration of the transaction or until another SET CONSTRAINTS statement resets the mode.

SET CONSTRAINTS ... IMMEDIATE causes the specified constraints to be checked immediately on execution of each constrained statement. Oracle first checks any constraints that were deferred earlier in the transaction and then continues immediately checking constraints of any further statements in that transaction, as long as all the checked constraints are consistent and no other SET CONSTRAINTS statement is issued. If any constraint fails the check, an error is signaled. At that point, a COMMIT causes the whole transaction to undo.

The ALTER SESSION statement also has clauses to SET CONSTRAINTS IMMEDIATE or DEFERRED. These clauses imply setting ALL deferrable constraints (that is, you cannot specify a list of constraint names). They are equivalent to making a SET CONSTRAINTS statement at the start of each transaction in the current session.

Making constraints immediate at the end of a transaction is a way of checking whether COMMIT can succeed. You can avoid unexpected rollbacks by setting constraints to IMMEDIATE as the last statement in a transaction. If any constraint fails the check, you can then correct the error before committing the transaction.

The SET CONSTRAINTS statement is disallowed inside of triggers.

SET CONSTRAINTS can be a distributed statement. Existing database links that have transactions in process are told when a SET CONSTRAINTS ALL statement occurs, and new links learn that it occurred as soon as they start a transaction.