INITIALLY DEFERRED

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as report

 

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

 

INITIALLY DEFERRED 表示在事物结束的时候才去检查约束。

 

SQL> desc cust;
Name               Type        

---------          ------------

CUST_ID            NUMBER(2)                             
CUST_NAME       VARCHAR2(15)                        
E_NAME             LONG        

 

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','R');

1 row inserted

SQL> INSERT INTO cust VALUES (1,'RAJ','R');

1 row inserted

SQL> INSERT INTO cust VALUES (1,'RAJ','R');

1 row inserted

 

虽然设置了cust_id是主键，在事务未提交前我们还可以插入三条id相同的记录。

SQL> SELECT * FROM CUST;

CUST_ID CUST_NAME       E_NAME
------- --------------- ----------
      1 RAJ             R
      1 RAJ             R
      1 RAJ             R

但在事物提交的时候，会去检查约束。

SQL> COMMIT;

COMMIT

ORA-02091: 事务处理已回退
ORA-00001: 违反唯一约束条件 (REPORT.CUST_ID_PK)

 

 

实际并没有记录被插进来。

SQL> SELECT * FROM CUST;

CUST_ID CUST_NAME       E_NAME
------- --------------- ----------

 

 

SQL> INSERT INTO cust VALUES (1,'RAJ','R');

1 row inserted

SQL> COMMIT;

Commit complete

 

设置约束检查为IMMEDIATE后，会在insert后立即校验，而不是在事务结束时校验。

 

SQL> SET CONSTRAINT cust_id_pk IMMEDIATE;

Constraints set

SQL> INSERT INTO cust VALUES (1,'RAJ','R');

INSERT INTO cust VALUES (1,'RAJ','R')

ORA-00001: 违反唯一约束条件 (REPORT.CUST_ID_PK)

 

SQL> INSERT INTO cust VALUES (2,'ORACLE','O');

1 row inserted

SQL> COMMIT;

Commit complete

SQL> SELECT * FROM CUST;

CUST_ID CUST_NAME       E_NAME
------- --------------- ----------
      1 RAJ             R
      2 ORACLE          O