alter table 语句中的deferable initially deferred

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 (3,'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
答案是C

其中 alter table 语句的DEFERRABLE INITIALLY DEFERRED 的官方描述：

The DEFERRABLE and NOT DEFERRABLE parameters indicate whether or not, in subsequent transactions, constraint checking can be deferred until the end of the transaction using the SET CONSTRAINT(S) statement. If you omit this clause, then the default is NOT DEFERRABLE.

IMMEDIATE

Specify IMMEDIATE to cause the specified constraints to be checked immediately on execution of each constrained DML statement. Oracle Database 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, then an error is signaled. At that point, a COMMIT statement causes the whole transaction to undo.

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.

DEFERRED

Specify DEFERRED to indicate that the conditions specified by the deferrable constraint are checked when the transaction is committed.

The INITIALLY clause establishes the default checking behavior for constraints that are DEFERRABLE. The INITIALLY 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 TABLE statement is committed or the statement will fail.

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

This clause is not valid if you have declared the constraint to be NOT DEFERRABLE, because a NOT DEFERRABLE constraint is automatically INITIALLY IMMEDIATE and cannot ever be INITIALLY DEFERRED.

有此可以知道 由于在添加约束的时候指定了DEFERRABLE INITIALLY DEFERRED ，即初始延迟；每次只要不显示执行SET CONSTRAINT 。。。IMMEDIATE命令，在语句执行的时候不会执行约束，只有在commit时，即事务要提交时才会进行约束检查 ; 当commit 时，有失败的语句（不符合约束的行），那么会自动回滚整个事务的语句； 所以最好在commit之前执行SET CONSTRAINT 。。。IMMEDIATE语句，之后发出错误信息，不会回滚，这是可以修改不符合约束的行，然后再提交；

注：在事务结束后（提交或回滚）后，SET CONSTRAINT 。。。IMMEDIATE|DEFERRED 的作用也即结束，接着对应的约束还是会按照初始的设置；

如果是下面这种情况

SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1,'LATA'); --row 3
INSERT INTO cust VALUES (1,'KING'); --row 4
COMMIT;

那么只有第三行会进入表中；