1. 兩種驗證時機.
Oracle的constraints(限制) 根據驗證時機可以分成兩種.
case 1. 在每一句insert statement 執行時就會馬上驗證, 如果限制驗證失敗, 則這句sql statement 會執行失敗.
case 2. 執行insert statements 時不會驗證, 在commit的時候驗證, 如果驗證失敗, 則整個Transaction 復原.
2.constraints的分類
對應地, oracle 的 constraints 也可以分成兩大類.
一種是not deferrable (不可以延時的) . 這種情況下隻能執行 case1 的驗證時機(即時驗證)
另一種是 defferable (可以設定成延時的). 這種情況下可以執行 case 1 或 case2 的驗證時機. 但需要設定.
對于第二種defferable 分類, 還可以分成兩小類.
一種是 initially immediate , 意思時預設情況下執行case 1.
另一種是initially deferred, 意思是預設情況下執行case2.
也就是可以分成三種
1. not deferrable
2. deferrable initially immediate
3. deferrable initially deferred.
如下圖:
下面會舉一些例子來詳細解釋這個3種 constraints的差別:
2.1 not deferrable constraints
這種最常見也最簡單. 如果在增加1個constraint 時不指定驗證時機屬性. 預設情況下就會被設為not deferrable.
既然constraint 是不可以延時驗證的, 是以也不用設定它的初始屬性(實際上就是initially immediate)
例子:
首先我們建立1張空表:
create table T1 (id number(10,0),
nm varchar(10));
接下來為id列 增加1個唯一限制t1_id:
SQL> alter table T1 add constraint t1_id unique(id);
Table altered.
注意這時并沒有指定 Deferrable 屬性, 是以預設情況下就是 not deferrable 的. 我們可以通過user_constraints視圖來檢查它的屬性:
SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';
TABLE_NAME CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1 T1_ID ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED
注意Deferrable 屬性是 not deferrable. 而 Deferred 屬性(Initially 初始屬性) 是immediate.
這時我們連續執行3句insert sql:
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated
可以見到第一第二句insert statement 能正常執行, 但第三句執行失敗, 因為id=2 與第二句重複了, 限制驗證失敗.
是以說驗證時機屬于case1, 即時驗證.
這種驗證時機也是最常見的.
2.2 deferrable initially immediate constraints
這種限制預設情況下等于第一種限制(not deferrable), 但是它可以在事務中改變設定, 變成延時驗證的限制.
我們可以這樣了解.
第一個屬性deferrable 表示 這個限制可以被設定成延時驗證.
第二個屬性initially immediate 表示這個限制預設情況下是即時驗證.
我們繼續利用上面的例子.
首先, 清空上面那個例子表. 由于上面操作沒有送出, 是以復原就ok了.
SQL> rollback;
Rollback complete.
然後我們修改(删除再建立)那個限制, 射程deferrable initiallly immediate 模式.
SQL> alter table T1 drop constraint t1_id;
Table altered.
SQL> alter table T1 add constraint t1_id unique(id) initially immediate deferrable ;
Table altered.
SQL>
這時我們去user_constraints視圖檢查屬性:
SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';
TABLE_NAME CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1 T1_ID ENABLED DEFERRABLE IMMEDIATE VALIDATED
可以見到隻有1個屬性與Not Defferable constraint有差別, 就是 deferrable 屬性是 deferrable了. 但是 deferred (初始屬性) 還是 immediate.
我們繼續嘗試插入上面的三行資料:
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated
可以到執行結果與上面一模一樣. 沒有差別. 也就是說也是case 1模式.
我們檢索這張表, 隻有兩行資料, 然後復原, 那麼就清空這張表了.
SQL> select * from T1;
ID NM
---------- ----------
1 abc1
2 abc2
SQL> rollback;
Rollback complete.
SQL> select * from T1;
no rows selected
SQL>
好了, 現在就是重點了, deferrable可以在事務中臨時改變成另1種模式.
也就是說可以由case1 臨時設定為 case 2.
syntax:
set constraint <constraint_name> deferred/immediate
注意上面的語句并沒有改變這個constraint的任何屬性, 隻不過是切換為另一種模式
也就是說初始是immediate模式的, 執行上面的語句後就臨時變成deferred模式了.
我們接回上面的例子, 執行另一隻模式:
SQL> set constraint t1_id deferred;
Constraint set.
我們查下這個constraint的屬性, 發現屬性完全無改變, Deferred屬性還是immediate. 因為Deferred這個屬性指的是初始屬性.
SQL> select table_name, constraint_name, status, deferrable, deferred, validated from user_constraints where table_name = 'T1';
TABLE_NAME CONSTRAINT_NAME STATUS DEFERRABLE DEFERRED VALIDATED
---------- --------------- -------- -------------- --------- -------------
T1 T1_ID ENABLED DEFERRABLE IMMEDIATE VALIDATED
這時我們insert上面的三條記錄. 發現3條insert都可以被插入到資料表中.
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
1 row created.
SQL> select * from T1;
ID NM
---------- ----------
1 abc1
2 abc2
2 abc3
SQL>
則說明這時在insert 語句執行時不會執行這個限制的驗證, 三行都被插入到表中了, 但是在送出時回驗證.
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (BILL.T1_ID) violated
見到送出失敗, 而且整個事務會復原, 也就是說這時表裡面連第一第二句的insert記錄也沒有了.
SQL> select * from T1;
no rows selected
這個跟not defferable constraint有差別啊, 那種在每一句sql statement執行驗證, 而且驗證失敗時并不會復原事務.
好了下面也是重點:
當我們commit失敗後被復原, 也就是代表目前的事務結束了.
而上面我們提到過, 使用 set constraint 語句 切換constraint到另一種模式隻會在目前事務中生效.
也就是指, 事務結束後, 這個constraint就會傳回初始模式!
我們再嘗試插入三條語句:
SQL> insert into T1 values(1, 'abc1');
1 row created.
SQL> insert into T1 values(2, 'abc2');
1 row created.
SQL> insert into T1 values(2, 'abc3');
insert into T1 values(2, 'abc3')
*
ERROR at line 1:
ORA-00001: unique constraint (BILL.T1_ID) violated
發現再次在第三句執行時被執行即時驗證, 因為在新事務中, constraint t1_id又切換回 immediate 模式了.
2.3 deferrable initially deferred constraints
如果了解了第二種constraint, 那麼最後一種就很簡單了
第一個屬性deferrable 表示 這個限制可以被設定成延時驗證.
第二個屬性initially immediate 表示這個限制預設情況下是延時驗證.
但是可以用set constraint語句 在1個事務中臨時切換為 即時驗證.
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)