deferrable约束的 deferred/immdiate状态测试
======================
SQL> create table t_12 (a number, b varchar2(1));
表已创建。
SQL> alter table t_12 add constraint pk_t_12 primary key (a) deferrable initially immediate;
表已更改。
==这里建立了可延迟约束,初始状态是语句级立即约束
SQL> insert into t_12 values (1,'a');
已创建 1 行。
SQL> commit;
提交完成。
SQL> insert into t_12 values (1,'b');
insert into t_12 values (1,'b')
*
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T_12)
SQL> set constraint pk_t_12 deferred;
约束条件已设置。
==在当前事务中将约束pk_t_12置为延迟约束
SQL> insert into t_12 values (1,'b');
已创建 1 行。
SQL> select * from t_12;
A B
---------- -
1 a
1 b
SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已重算
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T_12)
SQL> insert into t_12 values (1,'b');
insert into t_12 values (1,'b')
*
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T_12)
== 可以看到在前面提交了commit,事务结束,pk_t_12又回到了语句级立即约束状态
SQL>
SQL> alter session set constraints=deferred;
会话已更改。
== 在当前会话中将约束pk_t_12置为延迟约束
SQL> insert into t_12 values (1,'b');
已创建 1 行。
SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已重算
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T_12)
SQL> insert into t_12 values (1,'b');
已创建 1 行。
== 前面的事务已经提交了,但约束pk_t_12在同一会话下的另一个事务中
== 还是为延迟状态
SQL> commit;
commit
*
ERROR 位于第 1 行:
ORA-02091: 事务处理已重算
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T_12)
另外打开一个session
SQL> insert into t_12 values (1,'a');
insert into t_12 values (1,'a')
*
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T_12)
== 约束pk_t_12在另一个session中是初始的立即约束状态
enable/disable 与 validate/novalite的测试
======================
SQL> alter table t_12 modify constraint pk_t_12 disable validate;
表已更改。
SQL> insert into t_12 values (1,'a');
insert into t_12 values (1,'a')
*
ERROR 位于第 1 行:
ORA-25128: 不能对带有禁用和验证约束条件 (SCOTT.PK_T_12) 的表进行插入/更新/删除
== disable validate状态下不能对表进行DML操作
== 但可以查询,某些情况下可运用于数据仓库
SQL> select * from t_12;
A B
---------- -
1 a
SQL> alter table t_12 modify constraint pk_t_12 disable novalidate;
表已更改。
SQL> insert into t_12 values (1,'a');
已创建 1 行。
== disable novalidate状态下新旧数据不需要满足约束
== 可以DML
SQL> insert into t_12 values (1,'a');
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter table t_12 modify constraint pk_t_12 enable novalidate;
表已更改。
== 表里有不满足约束的旧数据,但可以切换到enable novalidate状态
== 不满足约束的新数据插入是不允许的
SQL> insert into t_12 values (1,'a');
insert into t_12 values (1,'a')
*
ERROR 位于第 1 行:
ORA-00001: 违反唯一约束条件 (SCOTT.PK_T_12)
SQL> alter table t_12 modify constraint pk_t_12 enable validate;
alter table t_12 modify constraint pk_t_12 enable validate
*
ERROR 位于第 1 行:
ORA-02437: 无法验证 (SCOTT.PK_T_12) - 违反主键
== 表里有不满足约束的旧数据,不能切换到enable novalidate状态
SQL> delete from t_12 where rownum<3;
已删除2行。
SQL> commit;
提交完成。
SQL> select * from t_12;
A B
---------- -
1 a
SQL> alter table t_12 modify constraint pk_t_12 enable novalidate;
表已更改。
== 清理掉表里不满足约束的旧数据,可以切换到enable validate状态
enable/disable 约束对索引的影响
=================
== 下面我们会测试三种不同情况下disable约束对索引的影响
SQL> create table t_14 (a number primary key ,b varchar2(1));
表已创建。
SQL> select constraint_name,constraint_type,table_name,status, deferrable ,deferred from user_constraints where
2 table_name ='T_14';
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE DEFERRED
------------------------------ - ------------------------------ -------- -------------- ---------
SYS_C003431 P T_14 ENABLED NOT DEFERRABLE IMMEDIATE
SQL> select index_name,index_type,UNIQUENESS,status,table_name from user_indexes where table_name ='T_14';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS TABLE_NAME
------------------------------ --------------------------- --------- -------- ------------------------------
SYS_C003431 NORMAL UNIQUE VALID T_14
== 可以看到系统对该主键约束自动建立了一条唯一性索引
== 下面我们disable该约束
SQL> alter table t_14 disable constraint sys_c003431;
表已更改。
SQL> select constraint_name,constraint_type,table_name,status, deferrable ,deferred from user_constraints where
2 table_name ='T_14';
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE DEFERRED
------------------------------ - ------------------------------ -------- -------------- ---------
SYS_C003431 P T_14 DISABLED NOT DEFERRABLE IMMEDIATE
SQL> select index_name,index_type,UNIQUENESS,status,table_name from user_indexes where table_name ='T_14';
未选定行
== disable该约束后可以看到索引也被自动删除
SQL> alter table t_14 enable constraint sys_c003431;
表已更改。
SQL> select index_name,index_type,UNIQUENESS,status,table_name from user_indexes where table_name ='T_14';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS TABLE_NAME
------------------------------ --------------------------- --------- -------- ------------------------------
SYS_C003431 NORMAL UNIQUE VALID T_14
SQL> select constraint_name,constraint_type,table_name,status, deferrable ,deferred from user_constraints where
2 table_name ='T_14';
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE DEFERRED
------------------------------ - ------------------------------ -------- -------------- ---------
SYS_C003431 P T_14 ENABLED NOT DEFERRABLE IMMEDIATE
== enable约束后,可以看到索引也被自动建立起来
SQL> create index idx_t_14 on t_14 (b);
索引已创建。
SQL> select constraint_name,constraint_type,table_name,status, deferrable ,deferred from user_constraints where table_name= 'T_14';
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE DEFERRED
------------------------------ - ------------------------------ -------- -------------- ---------
SYS_C003431 P T_14 ENABLED NOT DEFERRABLE IMMEDIATE
SQL> select index_name,index_type,UNIQUENESS,status,table_name from user_indexes where table_name ='T_14';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS TABLE_NAME
------------------------------ --------------------------- --------- -------- ------------------------------
IDX_T_14 NORMAL NONUNIQUE VALID T_14
SYS_C003431 NORMAL UNIQUE VALID T_14
SQL> alter table t_14 disable constraint SYS_C003431;
表已更改。
SQL> select index_name,index_type,UNIQUENESS,status,table_name from user_indexes where table_name ='T_14';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS TABLE_NAME
------------------------------ --------------------------- --------- -------- ------------------------------
IDX_T_14 NORMAL NONUNIQUE VALID T_14
== disble该约束后可以看到只有对应的索引被自动删除,手工建立的索引仍然存在并有效
== 下面我们建立一个可延迟约束的表t_15进行测试
SQL> create table t_15 (a number primary key deferrable initially immediate,b varchar2(1));
表已创建。
SQL> select constraint_name,constraint_type,table_name,status, deferrable ,deferred from user_constraints where table_name= 'T_15';
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE DEFERRED
------------------------------ - ------------------------------ -------- -------------- ---------
SYS_C003433 P T_15 ENABLED DEFERRABLE IMMEDIATE
SQL> select index_name,index_type,UNIQUENESS,status,table_name from user_indexes where table_name ='T_15';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS TABLE_NAME
------------------------------ --------------------------- --------- -------- ------------------------------
SYS_C003433 NORMAL NONUNIQUE VALID T_15
== 系统自动建立了一个非唯一索引
SQL> alter table t_15 disable constraint SYS_C003433;
表已更改。
SQL> select constraint_name,constraint_type,table_name,status, deferrable ,deferred from user_constraints where table_name= 'T_15';
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE DEFERRED
------------------------------ - ------------------------------ -------- -------------- ---------
SYS_C003433 P T_15 DISABLED DEFERRABLE IMMEDIATE
SQL> select index_name,index_type,UNIQUENESS,status,table_name from user_indexes where table_name ='T_15';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS TABLE_NAME
------------------------------ --------------------------- --------- -------- ------------------------------
SYS_C003433 NORMAL NONUNIQUE VALID T_15
== 我们把主键约束SYS_C003433给disable了,但是对应的约束并未删除且仍然有效
约束学习
最新推荐文章于 2023-11-21 22:09:29 发布