Oracle约束的4种状态小结
来源:互联网 发布:java log4j 用法 编辑:程序博客网 时间:2024/04/30 22:27
约束的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 systemSQL> 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会自动创建唯一性索引)
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)
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 BOSTON
SQL>
REF: <<Expert Oracle Database 11g Administration>> Page 308, Integrity Constraint States
- Oracle约束的4种状态小结
- Oracle约束的4种状态小结
- Oracle-约束小结
- oracle constraint 约束 状态 详解
- 完整性约束的状态
- 完整性约束的状态
- oracle的五种约束
- oracle数据库的5种约束类型
- ORACLE的完整性约束
- Oracle 的约束
- ORACLE的约束
- ORACLE约束的属性
- oracle的约束
- oracle的表约束
- Oracle数据库的约束
- oracle约束的分类
- oracle的check约束
- oracle的unique约束
- 设置输入法弹出时,处于焦点状态的控件所在界面保持原样
- Singleton 单例模式 -IOS开发
- pointer endianess sizeof()
- 给android设备增加串口功能
- 深入JVM之根类加载器的测试
- Oracle约束的4种状态小结
- 如何编写HTTP Servlet程序
- ftok的陷阱
- 对INIT_WORK的理解
- 深入JVM之类的主动使用和final关键字
- 开源软件_源码SVN地址
- 来来来
- 汇接局、端局、关口局
- Android 系统对底层按键的处理方法