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 bSQL> 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)另外打开一个sessionSQL> 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 aSQL> 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 aSQL> 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 IMMEDIATESQL> 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 IMMEDIATESQL> 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_14SQL> 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 IMMEDIATESQL> 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_14SYS_C003431 NORMAL UNIQUE VALID T_14SQL> 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 IMMEDIATESQL> 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 IMMEDIATESQL> 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了，但是对应的约束并未删除且仍然有效
