一、简述
Oracle约束状态由四个选项决定：enable、disable、validate、invalidate，其中validate or invalidate 状态效果依赖于enable or disable 。
disable novalidate 既不会约束新增数据也不会验证已有数据
disable validate 启用后禁止DML
enable novalidate 约束新增数据但不会验证已有数据
enable validate 约束新增数据并验证已有数据
参考资料：Oracle 11g官方文档、http://blog.csdn.net/shangzhiliang_2008/article/details/8163375
二、练习
创建测试对象：
[oracle@DBTS ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.3.0 Production on Sun May 24 10:50:17 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
scott@DBTS> create table t_dept as select * from dept;
scott@DBTS> select * from t_dept;
DEPTNO
DNAME LOC
---------- -------------- -------------
10 ACCOUNTING
NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
scott@DBTS> alter table t_dept add constraint uk_t_dept unique(deptno);
scott@DBTS>select index_name,table_name,uniqueness from user_indexes where
index_name = 'UK_T_DEPT'
INDEX_NAME
TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
UK_T_DEPT
T_DEPT UNIQUE
----若不指定，默认为enable、validate，验证新增数据--------------------------------------------
scott@DBTS>select
owner,constraint_name,constraint_type,status,validated from user_constraints
where constraint_name = 'UK_T_DEPT' ;
OWNER CONSTRAINT_NAME C
STATUS VALIDATED
---------- ------------------------------ - ---------- -------------
SCOTT
UK_T_DEPT
U ENABLED VALIDATED
scott@DBTS> insert into t_dept(deptno) values(10);
insert into t_dept(deptno) values(10)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_T_DEPT) violated
----disable novalidate不验证新增数据，drop唯一索引-----------------------------------
scott@DBTS> alter table t_dept modify constraint uk_t_dept
disable novalidate;
Table altered.
scott@DBTS> insert into t_dept(deptno) values(10);
1 row created.
scott@DBTS> select * from t_dept;
DEPTNO
DNAME LOC
---------- -------------- -------------
10
ACCOUNTING NEW YORK
20
RESEARCH DALLAS
30
SALES CHICAGO
40
OPERATIONS BOSTON
10
scott@DBTS> select index_name,table_name,uniqueness from
user_indexes where index_name = 'UK_T_DEPT';
no rows selected
----enable novalidate状态校验已有数据,enable试图创建唯一性索引，而已有数据deptno存在重复数据10,报错--------------------------------------------------------------------------
scott@DBTS> alter table t_dept modify constraint uk_t_dept enable
novalidate;
alter table t_dept modify constraint uk_t_dept enable novalidate
*
ERROR at line 1:
ORA-02299: cannot validate (SCOTT.UK_T_DEPT) - duplicate keys found
----enable novalidate状态建立唯一索引，验证新增数据--------------------------------------
scott@DBTS>delete t_dept where loc is null
scott@DBTS> alter table t_dept modify constraint uk_t_dept enable novalidate;
scott@DBTS>select index_name,table_name,uniqueness from user_indexes where index_name = 'UK_T_DEPT'
INDEX_NAME
TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
UK_T_DEPT
T_DEPT UNIQUE
scott@DBTS>
insert into t_dept(deptno) values(10);
insert into t_dept(deptno) values(10)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_T_DEPT) violated
----disable validate 状态drop唯一索引，禁止DML操作--------------------------------------------
scott@DBTS> alter table t_dept modify constraint uk_t_dept disable validate;
scott@DBTS>scott@DBTS> select index_name,table_name,uniqueness from user_indexes where index_name = 'UK_T_DEPT';
no rows selected
scott@DBTS> insert into t_dept(deptno) values(10);
insert into t_dept(deptno) values(10)
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (SCOTT.UK_T_DEPT)
disabled and validated
---- enable validate 创建唯一索引，验证新增数据-----------------------------------------------------
scott@DBTS> alter table t_dept modify constraint uk_t_dept enable
validate;
Table altered.
scott@DBTS> select index_name,table_name,uniqueness from user_indexes where
index_name = 'UK_T_DEPT';
INDEX_NAME
TABLE_NAME UNIQUENES
------------------------------ ---------- ---------
UK_T_DEPT
T_DEPT UNIQUE
scott@DBTS> insert into t_dept(deptno) values(10);
insert into t_dept(deptno) values(10)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_T_DEPT) violated
----当外键依赖于disable状态的唯一或主键约束时，创建外键不能成功------------------
scott@DBTS> alter table t_dept modify constraint uk_t_dept
disable validate;
scott@DBTS> select owner,constraint_name,constraint_type,status,validated
from user_constraints where constraint_name = 'UK_T_DEPT' ;
OWNER CONSTRAINT_NAME CONSTRAINT_TYPE
STATUS VALIDATED
---------- ---------------- ---------------- ---------- -------------
SCOTT
UK_T_DEPT
U
DISABLED VALIDATED
scott@DBTS> create table t_emp as select * from emp where rownum<6;
scott@DBTS> select * from t_emp;
EMPNO ENAME
JOB
MGR HIREDATE
SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ----------
----------
7369 SMITH
CLERK 7902
17-DEC-80
800
20
7499 ALLEN
SALESMAN 7698 20-FEB-81
1600
300 30
7521 WARD
SALESMAN 7698
22-FEB-81
1250 500
30
7566 JONES
MANAGER 7839
02-APR-81
2975
20
7654 MARTIN
SALESMAN 7698 28-SEP-81
1250
1400 30
scott@DBTS> alter table t_dept drop constraint uk_t_dept;
scott@DBTS> alter table t_dept add constraint pk_t_dept primary
key(deptno);
scott@DBTS> alter table t_dept modify constraint pk_t_dept disable validate;
scott@DBTS>alter table t_emp add constraint fk_t_emp foreign
key(deptno) references t_dept
alter table t_emp add constraint fk_t_emp foreign key(deptno) references t_dept
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
外键创建失败
scott@DBTS> alter table t_dept modify constraint pk_t_dept enable
validate;
Table altered.
scott@DBTS> alter table t_emp add constraint fk_t_emp foreign key(deptno)
references t_dept;
Table altered.
将t_dept主键enable，t_emp外键创建成功
scott@DBTS> alter table t_dept modify constraint pk_t_dept disable validate;
alter table t_dept modify constraint pk_t_dept disable validate
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SCOTT.PK_T_DEPT) - dependencies exist
企图将t_dept主键disable，报错，外键依赖
此外，优化器可以在enable validate状态使用约束作用（官方文档）
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30187411/viewspace-1669380/ ，如需转载，请注明出处，否则将追究法律责任。
