Oracle约束状态,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.youkuaiyun.com/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，验证新增数据---