disable/enable validate/novalidate 的區別 (ORACLE)

啟用約束:
enable( validate) :啟用約束,建立索引,對已有及新加入的資料執行約束.
enable novalidate :啟用約束,建立索引,僅對新加入的資料強制執行約束,而不管表中的現有資料.

禁用約束:
disable( novalidate):關閉約束,刪除索引,可以對約束列的資料進行修改等操作.
disable validate :關閉約束,刪除索引,不能對錶進行 插入/更新/刪除等操作.

環境:oracle 9i 9.0.1.0 for win,以上結論均測試通過.
例:disable validate約束後,執行update...操作提示:
ORA-25128: 不能對帶有禁用和驗證約束條件 (SYS.PK_EMP_01) 的表進行插入/更新/刪除

=============================================================

首先對constraint的開啟和關閉共有四種:enable validate;enable novalidate;disable validate;disable novalidate;
1,3沒有什麼好說的,2表示開啟時不檢查已存在資料,4表示不檢查今後的資料.
為此做如下測試:
1.首先建立測試表
create table a (no number(5),name varchar2(10));
insert into a values(1,'ss');
alter table a add constraint pk_a primary key(no);

create table b (no number(5),name varchar2(10));
insert into a values(1,'ss');
ALTER TABLE b ADD CONSTRAINT fk_a_b
FOREIGN KEY (no)
REFERENCES a (no);

2.測試fk
SQL> insert into b values(2,'sd');
insert into b values(2,'sd')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.FK_A_B) violated - parent key not found
為此使用
SQL> alter table b disable novalidate constraint fk_a_b;

Table altered.

insert into b values(2,'sdd')
SQL> /

1 row created.
SQL> alter table b enable novalidate constraint fk_a_b;

Table altered.
從上面測試結果可見enable novalidate和disable novalidate在fk等約束是好用的.
3.測試pk
SQL> alter table a disable novalidate primary key;

Table altered.
SQL> insert into a values(1,'sd');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table a enable novalidate primary key;
alter table a enable novalidate primary key
*
ERROR at line 1:
ORA-02437: cannot validate (SYSTEM.SYS_C001578) - primary key violated
在pk測試中enable novalidate不能使用了.
看到kamus的解釋說是enable novalidate主鍵必須是唯一.
查了下相關資料,沒有發現上述說明,但是找到一個下面的說法:
Primary and unique keys must use nonunique indexes
從新做測試
4.測試pk(2)
SQL> alter table a disable novalidate primary key;

Table altered.
這時我去檢查表a,發現主鍵對應的索引pk_a不在了.
手工建立索引
SQL> create index i_a on a(no);

Index created.

SQL> alter table a enable novalidate primary key;

Table altered.
成功了.

結論:
從上面的測試結果看出,novalidate在非pk;un時可以正常工作.
在對pk;un使用時需要先建立相關索引,再使用novalidate.

Validated Constraints
You have seen how to enable and disable a constraint. ENABLE and DISABLE
affect only future data that will be added/modified in the table. In contrast,
the VALIDATE and NOVALIDATE keywords in the ALTER TABLE command act
upon the existing data. Therefore, a constraint can have four states:

ENABLE VALIDATE This is the default for the ENABLE clause. The existing
data in the table is validated to verify that it conforms to the constraint.

ENABLE NOVALIDATE Does not validate the existing data, but enables
the constraint for future constraint checking.

DISABLE VALIDATE The constraint is disabled (any index used to
enforce the constraint is also dropped), but the constraint is kept valid.
No DML operation is allowed on the table because future changes cannot
be verified.

DISABLE NOVALIDATE This is the default for the DISABLE clause. The
constraint is disabled, and no checks are done on future or existing data.

eg:
ALTER TABLE WH01 MODIFY CONSTRAINT PK_WH01
DISABLE NOVALIDATE;