本文共 5038 字，大约阅读时间需要 16 分钟。
约束的4种状态：
disable novalidate 既不会约束新增数据也不会验证已有数据，等同于disable
enable novalidate 约束新增数据但不会验证已有数据
disable validate 约束新增数据但不会验证已有数据，启用后禁止DML
enable validate 约束新增数据并验证已有数据，等同于enable
扩展解释：
disable / enable 针对新增数据（对于唯一性约束，enable会自动创建唯一性索引，disable会自动drop索引）；
novalidate / validate 针对已有数据。
测试：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system SQL> drop table dept2; Table dropped SQL> create table dept2 as select * from scott.dept; Table created SQL> select * from dept2 order by deptno; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> alter table dept2 add constraint dept2_u1 unique(deptno); Table altered SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1'; INDEX_NAME TABLE_NAME UNIQUENESS ------------------------------ ------------------------------ ---------- DEPT2_U1 DEPT2 UNIQUE SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1'; CONSTRAINT_NAME STATUS VALIDATED ------------------------------ -------- ------------- DEPT2_U1 ENABLED VALIDATED SQL> insert into dept2(deptno) values(10); insert into dept2(deptno) values(10) ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated SQL> select * from dept2 order by deptno; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> alter table dept2 modify constraint dept2_u1 disable novalidate; Table altered SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1'; INDEX_NAME TABLE_NAME UNIQUENESS------------------------------ ------------------------------ ----------
（disable自动移除索引）
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1'; CONSTRAINT_NAME STATUS VALIDATED ------------------------------ -------- ------------- DEPT2_U1 DISABLED NOT VALIDATED SQL> insert into dept2(deptno) values(10); 1 row inserted SQL> select * from dept2 order by deptno; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 10 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> alter table dept2 modify constraint dept2_u1 enable novalidate; alter table dept2 modify constraint dept2_u1 enable novalidate ORA-02299: cannot validate (SYSTEM.DEPT2_U1) - duplicate keys found （因为enable会去创建唯一性索引，而已有数据deptno存在重复数据10，所以这里不能enable） SQL> delete from dept2 where deptno=10 and dname is null; 1 row deleted SQL> alter table dept2 modify constraint dept2_u1 enable novalidate; Table altered SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1'; INDEX_NAME TABLE_NAME UNIQUENESS ------------------------------ ------------------------------ ----------DEPT2_U1 DEPT2 UNIQUE
（enable会自动创建唯一性索引）
SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1'; CONSTRAINT_NAME STATUS VALIDATED ------------------------------ -------- ------------- DEPT2_U1 ENABLED NOT VALIDATED SQL> insert into dept2(deptno) values(10); insert into dept2(deptno) values(10) ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated SQL> alter table dept2 modify constraint dept2_u1 disable validate; Table altered SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1'; INDEX_NAME TABLE_NAME UNIQUENESS ------------------------------ ------------------------------ ---------- SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1'; CONSTRAINT_NAME STATUS VALIDATED ------------------------------ -------- ------------- DEPT2_U1 DISABLED VALIDATED SQL> insert into dept2(deptno) values(10); insert into dept2(deptno) values(10)ORA-25128: No insert/update/delete on table with constraint (SYSTEM.DEPT2_U1) disabled and validated
（disable validate后禁止DML）
SQL> select * from dept2 order by deptno; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> alter table dept2 modify constraint dept2_u1 enable validate; Table altered SQL> select index_name,table_name,uniqueness from dba_indexes where index_name = 'DEPT2_U1'; INDEX_NAME TABLE_NAME UNIQUENESS ------------------------------ ------------------------------ ---------- DEPT2_U1 DEPT2 UNIQUE SQL> select constraint_name,status,validated from dba_constraints where constraint_name = 'DEPT2_U1'; CONSTRAINT_NAME STATUS VALIDATED ------------------------------ -------- ------------- DEPT2_U1 ENABLED VALIDATED SQL> insert into dept2(deptno) values(10); insert into dept2(deptno) values(10) ORA-00001: unique constraint (SYSTEM.DEPT2_U1) violated SQL> select * from dept2 order by deptno; DEPTNO DNAME LOC ------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL>
REF: <<Expert Oracle Database 11g Administration>> Page 308, Integrity Constraint States
转载地址：http://pjtci.baihongyu.com/