一、简述
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,验证新增数据---