[20120824]oracle中的 CONSTRAINT 屬性ENABLE DISABLE VALIDATE NOVALIDATE.txt

[20120824]oracle中的 CONSTRAINT 屬性ENABLE DISABLE VALIDATE NOVALIDATE.txt

今天要在生產系統中建立一個約束，犯了一點點小錯誤，看書複習記錄一下：

我參考了這個連結：

http://space.itpub.net/13387766/viewspace-617654

一、組合特性說明：  

是否要求滿足約束         Validate                                Novalidate
------------------------------------------------------------------------------------
                        已有記錄     新增/修改記錄        已有記錄    新增/修改記錄
Enable                  Yes　    　  Yes                     No　         Yes
Disable                 Yes　        No                      No           No


Validate確保已有資料符合約束；
Novalidate不必考慮已有資料是否符合約束。
除非Novalidate被指定，Enable預設Validate；
除非Validate被指定，Disable預設Novalidate；

Validate和Novalidate對Enable和Disable沒有任何預設暗示。
Enable Validate與Enable相同，檢查已有記錄和新增記錄，確保都符合約束；
Enable Novalidate 允許已有記錄不必滿足約束條件，但新增/修改的記錄必須滿足；
Disable Validate禁用約束，刪除約束上的索引，不允許修改任何被約束的記錄；
Disable Novalidate與Disable相同，禁用約束，刪除約束上的索引，且允許修改被約束的記錄。


如果設定約束是Disable Validate這個特性很有意思，可以實現只讀表的功能。
不過11G允許設定表為只讀。

測試如下：
1.建立測試例子:
create table a (id number);
insert into a select rownum  from dual connect by level <=10;
commit ;

alter table a add constraint ck_a check (id<=100) disable;

2.測試：
SQL> select status,validated from dba_constraints where wner=user and constraint_name='CK_A';
STATUS   VALIDATED
-------- -------------
DISABLED NOT VALIDATED

--可以發現狀態是DISABLED和NOT VALIDATED，這樣可以DML資料：

SQL> insert into a values(199);
1 row created.
SQL> rollback ;
Rollback complete.

3.修改約束屬性:
SQL> alter table a disable validate constraint ck_a;
Table altered.


SQL> select status,validated from dba_constraints where wner=user and constraint_name='CK_A';
STATUS   VALIDATED
-------- -------------
DISABLED VALIDATED

--可以發現狀態是DISABLED和NOT VALIDATED，這樣就變成了只讀表。

SQL> insert into a values(199);
insert into a values(199)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated


SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated


SQL> update  a set id =10 where id=1;
update  a set id =10 where id=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated


SQL> delete from a where id=1;
delete from a where id=1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated

4.刪除約束：
SQL> alter table a drop constraint ck_a;
Table altered.

5.我前面使用約束條件是id<100.如果換成1=1就可以實現同樣的功能。
--SQL> alter table a add constraint ck_a check (1=1) disable novalidate;

SQL> alter table a add constraint ck_a check (1＝1) disable;
Table altered.

SQL> alter table a modify constraint ck_a  disable validate;
Table altered.

--oracle 的語法真奇怪，前面我使用的是alter table a disable validate constraint ck_a;也可以。
--還是這裡使用的語法更好1點。


SQL> insert into a values(19);
insert into a values(19)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.CK_A) disabled and validated

6.我的測試環境是11G：
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


SQL> alter table a drop constraint ck_a;
Table altered.

SQL> alter table a read only ;
Table altered.

SQL> insert into a values(19);
insert into a values(19)
            *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."A"

--取消只讀特性。
SQL> alter table a read write  ;
Table altered.