溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
一、Oracle約束的狀態
Oracle完整性約束的狀態有4種，分別是ENABLE、DISABLE、VALIDATE、NOVALIDATE。
ENABLE 表示Oracle將檢查要插入或更新的數據庫中的數據是否符合約束；
DISABLE 表示表中可以存放違反約束的行；
VALIDATE 表示數據庫驗證表中的已存在數據是否符合約束；
NOVALIDATE 表示數據庫不驗證表中已存在數據是否符合約束。
Oracle默認約束狀態為ENABLE、VALIDATE。
下面看Oracle官方給出的匯總：
| Modified Data | Existing Data | Summary |
|---|---|---|
|
| Existing and future data must obey the constraint. An attempt to apply a new constraint to a populated table results in an error if existing rows violate the constraint. |
|
| The database checks the constraint, but it need not be true for all rows. Thus, existing rows can violate the constraint, but new or modified rows must conform to the rules. |
|
| The database disables the constraint, drops its index, and prevents modification of the constrained columns. |
|
| The constraint is not checked and is not necessarily true. |
下面使用實例測試各狀態：
創建測試表
zx@ORA11G>create table t1 (id number,name varchar2(10),address varchar2(10)); Table created. zx@ORA11G>insert into t1 values(1,'zx','hb'); 1 row created. zx@ORA11G>insert into t1 values(1,'zq','jx'); 1 row created. zx@ORA11G>insert into t1 values(2,'wl','sd'); 1 row created. zx@ORA11G>commit; Commit complete.
1、測試ENABLE、VALIDATE狀態
zx@ORA11G>alter table t1 add constraint t1_uk unique(id); alter table t1 add constraint t1_uk unique(id) * ERROR at line 1: ORA-02299: cannot validate (ZX.T1_UK) - duplicate keys found
因為id列中有重復值，此時創建約束t1_uk的狀態為ENABLE、VALIDATE會驗證表中已存在的數據，所以創建約束不成功。刪除表中的重復數據再次創建約束即可成功。
zx@ORA11G>delete from t1 where id=1 and name='zq'; 1 row deleted. zx@ORA11G>commit; Commit complete. zx@ORA11G>alter table t1 add constraint t1_uk unique(id); Table altered. zx@ORA11G>select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1'; TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED ------------------------------ ------------------------------ - -------------- -------- ------------- T1 T1_UK U NOT DEFERRABLE ENABLED VALIDATED
創建完成后再次插入id=1的數據即會報錯，說明約束狀態為ENABLE
zx@ORA11G>insert into t1 values(1,'zq','jx'); insert into t1 values(1,'zq','jx') * ERROR at line 1: ORA-00001: unique constraint (ZX.T1_UK) violated
2、測試ENABLE、DISABLED狀態
zx@ORA11G>select * from t1; ID NAME ADDRESS ---------- ---------- ---------- 1 zx hb 2 wl sd 1 zq jx zx@ORA11G>alter table t1 add constraint t1_uk unique(id) enable novalidate; alter table t1 add constraint t1_uk unique(id) enable novalidate * ERROR at line 1: ORA-02299: cannot validate (ZX.T1_UK) - duplicate keys found
直接創建unique約束報錯，因為有重復值。但先在id列上創建索引，然后創建unique約束即可成功。
zx@ORA11G>create index idx_t_id on t1(id); Index created. zx@ORA11G>alter table t1 add constraint t1_uk unique(id) using index idx_t_id enable novalidate; Table altered. zx@ORA11G>select * from t1; ID NAME ADDRESS ---------- ---------- ---------- 1 zx hb 2 wl sd 1 zq jx zx@ORA11G>select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1'; TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED ------------------------------ ------------------------------ - -------------- -------- ------------- T1 T1_UK U NOT DEFERRABLE ENABLED NOT VALIDATED
原表中的id列中有重復值，還是可以創建unique約束，因為狀態指定為NOVALIDATE，不驗證表中已有的數據。另外因為狀態為ENABLE，再次插入重復值報錯：
zx@ORA11G>insert into t1 values(2,'yc','bj'); insert into t1 values(2,'yc','bj') * ERROR at line 1: ORA-00001: unique constraint (ZX.T1_UK) violated
3、測試DISABLE、VALIDATE狀態
zx@ORA11G>select * from t1; ID NAME ADDRESS ---------- ---------- ---------- 1 zx hb 2 wl sd zx@ORA11G>alter table t1 add constraint t1_uk unique(id) using index idx_t_id disable validate; Table altered. zx@ORA11G>select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1'; TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED ------------------------------ ------------------------------ - -------------- -------- ------------- T1 T1_UK U NOT DEFERRABLE DISABLED VALIDATED zx@ORA11G>insert into t1 values(1,'zq','jx'); insert into t1 values(1,'zq','jx') * ERROR at line 1: ORA-25128: No insert/update/delete on table with constraint (ZX.T1_UK) disabled and validated
DISABLE、VALIDATE狀態下，不允許做增刪改操作。
4、測試DISABLE、NOVALIDATE狀態
zx@ORA11G>select * from t1; ID NAME ADDRESS ---------- ---------- ---------- 1 zx hb 2 wl sd 1 zq jx zx@ORA11G>alter table t1 add constraint t1_uk unique(id) using index idx_t_id disable novalidate; Table altered. zx@ORA11G>select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1'; TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED ------------------------------ ------------------------------ - -------------- -------- ------------- T1 T1_UK U NOT DEFERRABLE DISABLED NOT VALIDATED zx@ORA11G>insert into t1 values(2,'yc','bj'); 1 row created. zx@ORA11G>commit; Commit complete. zx@ORA11G>select * from t1; ID NAME ADDRESS ---------- ---------- ---------- 1 zx hb 2 wl sd 1 zq jx 2 yc bj
約束狀態為DISABLE、NOVALIDATE，對新數據和老數據都不做驗證。
二、驗證機制
1. 兩種驗證時機.
Oracle的constraints(約束) 根據驗證時機可以分成兩種.
case 1. 在每一句insert statement 執行時就會馬上驗證, 如果約束驗證失敗, 則這句sql statement 會執行失敗.
case 2. 執行insert statements 時不會驗證, 在commit的時候驗證, 如果驗證失敗, 則整個Transaction 回滾.
2.constraints的分類
對應地, oracle 的 constraints 也可以分成兩大類.
一種是not deferrable (不可以延時的) . 這種情況下只能執行 case1 的驗證時機(即時驗證)
另一種是 deferrable (可以設置成延時的). 這種情況下可以執行 case 1 或 case2 的驗證時機. 但需要設置.
對于第二種defferable 分類, 還可以分成兩小類.
一種是 initially immediate , 意思時默認情況下執行case 1.
另一種是initially deferred, 意思是默認情況下執行case2.
也就是可以分成三種，如下圖：
2.1、not deferrable
這種最常見也最簡單. 如果在增加1個constraint 時不指定驗證時機屬性. 默認情況下就會被設為not deferrable.既然constraint 是不可以延時驗證的, 所以也不用設定它的初始屬性(實際上就是initially immediate)。
清空上面的t1表，并創建一個unique約束
zx@ORA11G>truncate table t1; Table truncated. zx@ORA11G>select * from t1; no rows selected zx@ORA11G>alter table t1 add constraint t1_uk unique (id) not deferrable; Table altered. zx@ORA11G>select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1'; TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED ------------------------------ ------------------------------ - -------------- -------- ------------- T1 T1_UK U NOT DEFERRABLE ENABLED VALIDATED
約束為NOT DEFERRABLE狀態，插入測試數據查看狀態：
zx@ORA11G>insert into t1 values(1,'zx','hb'); 1 row created. zx@ORA11G>insert into t1 values(2,'wl','sd'); 1 row created. zx@ORA11G>insert into t1 values(1,'zq','jx'); insert into t1 values(1,'zq','jx') * ERROR at line 1: ORA-00001: unique constraint (ZX.T1_UK) violated zx@ORA11G>select * from t1; ID NAME ADDRESS ---------- ---------- ---------- 1 zx hb 2 wl sd
插入第三條數據時因為有重復數據，直接報錯，說明驗證時機為case1:即時驗證，但不會回滾之前插入的結果。
2.2、 deferrable、initially immediate狀態
zx@ORA11G>alter table t1 drop constraint t1_uk; Table altered. zx@ORA11G>alter table t1 add constraint t1_uk unique (id) deferrable initially immediate; Table altered. zx@ORA11G>select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1'; TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED ------------------------------ ------------------------------ - -------------- -------- ------------- T1 T1_UK U DEFERRABLE ENABLED VALIDATED zx@ORA11G>insert into t1 values(1,'zx','hb'); 1 row created. zx@ORA11G>insert into t1 values(2,'wl','sd'); 1 row created. zx@ORA11G>insert into t1 values(1,'zq','jx'); insert into t1 values(1,'zq','jx') * ERROR at line 1: ORA-00001: unique constraint (ZX.T1_UK) violated zx@ORA11G>select * from t1; ID NAME ADDRESS ---------- ---------- ---------- 1 zx hb 2 wl sd
插入第三條數據時報錯因為有重復值，說明驗證時機為case1:即時驗證，這與前一種狀態一樣。那為什么還要設置這樣一種狀態呢？我們來執行下面的語句：
zx@ORA11G>set constraint t1_uk deferred; Constraint set.
上面的語句并沒有改變這個constraint的任何屬性, 只不過是切換為另一種模式
也就是說初始是immediate模式的, 執行上面的語句后就臨時變成deferred模式了.
再次執行前面的插入語句：
zx@ORA11G>insert into t1 values(1,'zx','hb'); 1 row created. zx@ORA11G>insert into t1 values(2,'wl','sd'); 1 row created. zx@ORA11G>insert into t1 values(1,'zq','jx'); 1 row created.
第三條也能插入進去，下面嘗試commit：
zx@ORA11G>commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-00001: unique constraint (ZX.T1_UK) violated zx@ORA11G>select * from t1; no rows selected
commit時報錯，查詢t1表，沒有任何數據，說明回滾了整個事務。即case2：延遲驗證。此時再次執行上面的三次插入操作：
zx@ORA11G>insert into t1 values(1,'zx','hb'); 1 row created. zx@ORA11G>insert into t1 values(2,'wl','sd'); 1 row created. zx@ORA11G>insert into t1 values(1,'zq','jx'); insert into t1 values(1,'zq','jx') * ERROR at line 1: ORA-00001: unique constraint (ZX.T1_UK) violated
從上面結果可以看出，插入第三行時又報錯，說明上面的set constraint語句的作用范圍只有當前的一個事務。事務結束后即約束狀態即回到原模式。
2.3、deferrable、initially deferred
有了上面的第二個實驗就可以很容易的理解這一狀態了。
zx@ORA11G>alter table t1 drop constraint t1_uk; Table altered. zx@ORA11G>alter table t1 add constraint t1_uk unique (id) deferrable initially deferred; Table altered. zx@ORA11G>select table_name,constraint_name,constraint_type,deferrable,status,validated from user_constraints where table_name='T1'; TABLE_NAME CONSTRAINT_NAME C DEFERRABLE STATUS VALIDATED ------------------------------ ------------------------------ - -------------- -------- ------------- T1 T1_UK U DEFERRABLE ENABLED VALIDATED zx@ORA11G>insert into t1 values(1,'zx','hb'); 1 row created. zx@ORA11G>insert into t1 values(2,'wl','sd'); 1 row created. zx@ORA11G>insert into t1 values(1,'zq','jx'); 1 row created. zx@ORA11G>commit; commit * ERROR at line 1: ORA-02091: transaction rolled back ORA-00001: unique constraint (ZX.T1_UK) violated zx@ORA11G>select * from t1; no rows selected
參考：http://blog.csdn.net/nvd11/article/details/12654691
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT33337
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
