Oracle約束的狀態(tài)及驗證機(jī)制

一、Oracle約束的狀態(tài)

疏勒ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)建站的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：028-86922220（備注：SSL證書合作）期待與您的合作！

Oracle完整性約束的狀態(tài)有4種，分別是ENABLE、DISABLE、VALIDATE、NOVALIDATE。

ENABLE 表示Oracle將檢查要插入或更新的數(shù)據(jù)庫中的數(shù)據(jù)是否符合約束；
DISABLE 表示表中可以存放違反約束的行；
VALIDATE 表示數(shù)據(jù)庫驗證表中的已存在數(shù)據(jù)是否符合約束；
NOVALIDATE 表示數(shù)據(jù)庫不驗證表中已存在數(shù)據(jù)是否符合約束。

Oracle默認(rèn)約束狀態(tài)為ENABLE、VALIDATE。

下面看Oracle官方給出的匯總：

Modified Data	Existing Data	Summary
`ENABLE`	`VALIDATE`	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.
`ENABLE`	`NOVALIDATE`	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.
`DISABLE`	`VALIDATE`	The database disables the constraint, drops its index, and prevents modification of the constrained columns.
`DISABLE`	`NOVALIDATE`	The constraint is not checked and is not necessarily true.

下面使用實例測試各狀態(tài)：

創(chuàng)建測試表

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狀態(tài)

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列中有重復(fù)值，此時創(chuàng)建約束t1_uk的狀態(tài)為ENABLE、VALIDATE會驗證表中已存在的數(shù)據(jù)，所以創(chuàng)建約束不成功。刪除表中的重復(fù)數(shù)據(jù)再次創(chuàng)建約束即可成功。

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

創(chuàng)建完成后再次插入id=1的數(shù)據(jù)即會報錯，說明約束狀態(tài)為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狀態(tài)

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

直接創(chuàng)建unique約束報錯，因為有重復(fù)值。但先在id列上創(chuàng)建索引，然后創(chuàng)建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列中有重復(fù)值，還是可以創(chuàng)建unique約束，因為狀態(tài)指定為NOVALIDATE，不驗證表中已有的數(shù)據(jù)。另外因為狀態(tài)為ENABLE，再次插入重復(fù)值報錯：

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狀態(tài)

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狀態(tài)下，不允許做增刪改操作。

4、測試DISABLE、NOVALIDATE狀態(tài)

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

約束狀態(tài)為DISABLE、NOVALIDATE，對新數(shù)據(jù)和老數(shù)據(jù)都不做驗證。

二、驗證機(jī)制

1. 兩種驗證時機(jī).

Oracle的constraints(約束) 根據(jù)驗證時機(jī)可以分成兩種.

case 1. 在每一句insert statement 執(zhí)行時就會馬上驗證, 如果約束驗證失敗, 則這句sql statement 會執(zhí)行失敗.

case 2. 執(zhí)行insert statements 時不會驗證, 在commit的時候驗證, 如果驗證失敗, 則整個Transaction 回滾.

2.constraints的分類

對應(yīng)地, oracle 的 constraints 也可以分成兩大類.

一種是not deferrable (不可以延時的) . 這種情況下只能執(zhí)行 case1 的驗證時機(jī)(即時驗證)

另一種是 deferrable (可以設(shè)置成延時的). 這種情況下可以執(zhí)行 case 1 或 case2 的驗證時機(jī). 但需要設(shè)置.

對于第二種defferable 分類, 還可以分成兩小類.

一種是 initially immediate , 意思時默認(rèn)情況下執(zhí)行case 1.

另一種是initially deferred, 意思是默認(rèn)情況下執(zhí)行case2.

也就是可以分成三種，如下圖：

Oracle約束的狀態(tài)及驗證機(jī)制

2.1、not deferrable

這種最常見也最簡單. 如果在增加1個constraint 時不指定驗證時機(jī)屬性. 默認(rèn)情況下就會被設(shè)為not deferrable.既然constraint 是不可以延時驗證的, 所以也不用設(shè)定它的初始屬性(實際上就是initially immediate)。

清空上面的t1表，并創(chuàng)建一個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狀態(tài)，插入測試數(shù)據(jù)查看狀態(tài)：

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

插入第三條數(shù)據(jù)時因為有重復(fù)數(shù)據(jù)，直接報錯，說明驗證時機(jī)為case1:即時驗證，但不會回滾之前插入的結(jié)果。

2.2、 deferrable、initially immediate狀態(tài)

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

插入第三條數(shù)據(jù)時報錯因為有重復(fù)值，說明驗證時機(jī)為case1:即時驗證，這與前一種狀態(tài)一樣。那為什么還要設(shè)置這樣一種狀態(tài)呢？我們來執(zhí)行下面的語句：

zx@ORA11G>set constraint t1_uk deferred;

Constraint set.

上面的語句并沒有改變這個constraint的任何屬性, 只不過是切換為另一種模式

也就是說初始是immediate模式的, 執(zhí)行上面的語句后就臨時變成deferred模式了.

再次執(zhí)行前面的插入語句：

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.

第三條也能插入進(jìn)去，下面嘗試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表，沒有任何數(shù)據(jù)，說明回滾了整個事務(wù)。即case2：延遲驗證。此時再次執(zhí)行上面的三次插入操作：

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

從上面結(jié)果可以看出，插入第三行時又報錯，說明上面的set constraint語句的作用范圍只有當(dāng)前的一個事務(wù)。事務(wù)結(jié)束后即約束狀態(tài)即回到原模式。

2.3、deferrable、initially deferred

有了上面的第二個實驗就可以很容易的理解這一狀態(tài)了。

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

當(dāng)前名稱：Oracle約束的狀態(tài)及驗證機(jī)制
URL地址：http://jinyejixie.com/article2/gdjgic.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、外貿(mào)建站、小程序開發(fā)、移動網(wǎng)站建設(shè)、定制網(wǎng)站、建站公司

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

Oracle約束的狀態(tài)及驗證機(jī)制