Oracle中Constraint的狀態參數initially與deferrable

在Oracle資料庫中，關於約束的狀態有下面兩個參數：
            initially （initially immediate 或 initially deferred）
            deferrable（deferrable 或 not deferrable）
      第1個參數，指定預設情況下，約束的驗證時刻（在事務每條子句結束時，還是在整個事務結束時）。
      第2個參數，指定了在事務中，是否可以改變上一條參數的設定。
      如果不指定上述參數，預設設定是 initially immediate not deferrable。
      注意：如果約束是not deferrable，那麼它只能是initially immediate，而不能是initially deferred。

 

測試①，initially immediate：
SQL> create table nlist (
  2      nid number
  3  );
 
Table created
 
SQL> alter table nlist add constraint pk_nlist primary key (nid) initially immediate;
 
Table altered
 
SQL> insert into nlist values (1);
 
1 row inserted
 
SQL> insert into nlist values (1);
 
insert into nlist values (1)
 
ORA-00001: 違反唯一約束條件 (TEST.PK_NLIST)
 

測試②，initially deferred：
SQL> create table nlist (
  2      nid number
  3  );
 
Table created
 
SQL> alter table nlist add constraint pk_nlist primary key (nid) initially deferred;
 
Table altered
 
SQL> insert into nlist values (1);
 
1 row inserted
 
SQL> insert into nlist values (1);
 
1 row inserted
 
SQL> commit;
 
commit
 
ORA-02091: 交易處理已回退
ORA-00001: 違反唯一約束條件 (TEST.PK_NLIST)
 
測試③，initially immediate deferrable：
SQL> create table nlist (
  2      nid number
  3  );
 
Table created
 
SQL> alter table nlist add constraint pk_nlist primary key (nid) initially immediate deferrable;
 
Table altered
 
SQL> set constraint pk_nlist deferred;
 
Constraints set
 
SQL> insert into nlist values (1);
 
1 row inserted
 
SQL> insert into nlist values (1);
 
1 row inserted
 
SQL> commit;
 
commit
 
ORA-02091: 交易處理已回退
ORA-00001: 違反唯一約束條件 (TEST.PK_NLIST)
 
測試④，initially deferred deferrable：
SQL> create table nlist (
  2      nid number
  3  );
 
Table created
 
SQL> alter table nlist add constraint pk_nlist primary key (nid) initially deferred deferrable;
 
Table altered
 
SQL> set constraint pk_nlist immediate;
 
Constraints set
 
SQL> insert into nlist values (1);
 
1 row inserted
 
SQL> insert into nlist values (1);
 
insert into nlist values (1)
 
ORA-00001: 違反唯一約束條件 (TEST.PK_NLIST)

測試⑤：
SQL> create table nlist (
  2      nid number
  3  );
 
Table created
 
SQL> alter table nlist add constraint pk_nlist primary key (nid) initially deferred no deferrable;
 
alter table nlist add constraint pk_nlist primary key (nid) initially deferred no deferrable
 
ORA-01735: 無效的 ALTER TABLE 選項
轉載自http://hi.baidu.com/linkzq/item/be23df424c9312e3bdf4511c