Oracle constraint enable validate约束状态练习

一、简述

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博客 ” ，链接：http://blog.itpub.net/30187411/viewspace-1669380/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30187411/viewspace-1669380/