constraint 的disable,enable,validate,novalidate

一、组合特性说明：  


是否要求满足约束          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相同，禁用约束，删除约束上的索引，且允许修改被约束的记录。

先建两个有外键关联的测试表：

SQL> create table test_constraint (id number);
 
Table created
 
SQL> alter table test_constraint add constraint test_constraint$pk primary key(id);
 
Table altered

SQL> create table test_constraint2 (id number,rid number);
 
Table created
 
SQL> alter table test_constraint2 add constraint test$fk$test_constraint foreign key(rid) references test_constraint(id);
 
Table altered

SQL> insert into test_constraint values(1);
 
1 row inserted
 
SQL> insert into test_constraint values(2);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_constraint;
 
       ID
---------
        1
        2

SQL> insert into test_constraint2(id,rid) values(1,3);
 
insert into test_constraint2(id,rid) values(1,3)
 
ORA-02291: integrity constraint (DBOWN.TEST$FK$TEST_CONSTRAINT) violated - parent key not found

SQL> insert into test_constraint2(id,rid) values(1,3);
 
insert into test_constraint2(id,rid) values(1,3)
 
ORA-25128: No insert/update/delete on table with constraint (DBOWN.TEST$FK$TEST_CONSTRAINT) disabled and validated
 
SQL> select * from test_constraint2;
 
       ID       RID
--------- ---------
 
SQL> alter table test_constraint2 enable validate constraint test$fk$test_constraint;
 
Table altered

SQL> insert into test_constraint2(id,rid) values(1,1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_constraint2;
 
       ID       RID
--------- ---------
        1         1

SQL> insert into test_constraint2(id,rid) values(2,3);
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> update test_constraint2 set rid=2 where id=1;
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_constraint2;
 
       ID       RID
--------- ---------
        1         2
        2         3

SQL> alter table test_constraint2 enable novalidate constraint test$fk$test_constraint;
 
Table altered
 
SQL> insert into test_constraint2(id,rid) values(3,4);
 
insert into test_constraint2(id,rid) values(3,4)
 
ORA-02291: integrity constraint (DBOWN.TEST$FK$TEST_CONSTRAINT) violated - parent key not found

对已有记录不检录记录，新建记录则要满足约束。

SQL> insert into test_constraint2(id,rid) values(3,1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from test_constraint;
 
       ID
---------
        1
        2
 
SQL> select * from test_constraint2;
 
       ID       RID
--------- ---------
        1         2
        2         3
        3         1

SQL> alter table test_constraint2 enable validate constraint test$fk$test_constraint;
 
alter table test_constraint2 enable validate constraint test$fk$test_constraint
 
ORA-02298: cannot validate (DBOWN.TEST$FK$TEST_CONSTRAINT) - parent keys not found

记录2   3 不符合约束，所以不能enable validate.

SQL> alter table test_constraint2 disable validate constraint test$fk$test_constraint;
 
alter table test_constraint2 disable validate constraint test$fk$test_constraint
 
ORA-02298: cannot validate (DBOWN.TEST$FK$TEST_CONSTRAINT) - parent keys not found

一样记录2  3不能通过validate

SQL> delete test_constraint2 where id = 2;
 
1 row deleted
 
SQL>  alter table test_constraint2 disable validate constraint test$fk$test_constraint;
 
Table altered

把不符合约束的记录删掉就能disable validatte了

SQL> select * from test_constraint;
 
       ID
---------
        1
        2
 
SQL> select * from test_constraint2;
 
       ID       RID
--------- ---------
        1         2
        3         1
 
SQL> insert into test_constraint2(id,rid) values(3,3);
 
insert into test_constraint2(id,rid) values(3,3)
 
ORA-25128: No insert/update/delete on table with constraint (DBOWN.TEST$FK$TEST_CONSTRAINT) disabled and validated
 
SQL> insert into test_constraint2 (id,rid) values(4,4);
 
insert into test_constraint2 (id,rid) values(4,4)
 
ORA-25128: No insert/update/delete on table with constraint (DBOWN.TEST$FK$TEST_CONSTRAINT) disabled and validated
 
SQL> delete test_constraint2 where id=1;
 
delete test_constraint2 where id=1
 
ORA-25128: No insert/update/delete on table with constraint (DBOWN.TEST$FK$TEST_CONSTRAINT) disabled and validated
 
SQL> update test_constraint2 set rid = 1 where id = 1;
 
update test_constraint2 set rid = 1 where id = 1
 
ORA-25128: No insert/update/delete on table with constraint (DBOWN.TEST$FK$TEST_CONSTRAINT) disabled and validated
 竟然添加不了记录，不明白

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13387766/viewspace-617654/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13387766/viewspace-617654/