约束之禁用后，启用约束，问题数据插入异常表中的查询

约束的管理：约束按照对数据的严格性分为，延迟与立即约束

运行每句话都判定是立即约束，事务结束时统一判断是延迟约束；

 

 

测试1.1  =创建一个延迟约束

延迟约束：

select table_name,constraint_name,deferrable,deferred from user_constraints;

延迟约束可以容纳一段时间的非法数据，建立约束默认是立即约束；

 

 

改变约束的状态：

建立一个初始状态为延迟的可延迟约束；

create table a as select * from scott.emp;           ---创建一个测试表

             *

ERROR at line 1:

ORA-00955: name is already used by an existing object提示对象名称已被占用

drop table a

           *

ERROR at line 1:

ORA-00942: table or view does not exist对象不存在

SQL> select OBJECT_NAME,OBJECT_TYPE  from user_objects where OBJECT_NAME='A';

A                                     SYNONYM=同义词

drop synonym a;--删除

SQL> create table a as select * from scott.emp;       --创建测试表

alter table a add constraint u_empno unique(empno) initially deferred deferrable;

 

update a set empno=7900 where empno=7902;

commit                 

*

ERROR at line 1:

ORA-02091: transaction rolled back

ORA-00001: unique constraint (YANG.U_E) violated

 

--查看约束的状态：

SQL> select table_name,constraint_name,status,validated from user_constraints where table_name='A';

A                              U_EMPNO                     ENABLED           VALIDATED

 

 

---创建一个立即约束：对比查询：

alter table a add constraint li_ji unique(ename);

 

---查询验证：啥立即，啥延迟

select table_name,constraint_name,deferrable,deferred from user_constraints where table_name='A';

TABLE_NAME                     CONSTRAINT_NAME              DEFERRABLE          DEFERRED

A                                         U_EMPNO                             DEFERRABLE          DEFERRED

A                                              LI_JI                          NOT DEFERRABLE          IMMEDIATE

 

 

小结：延迟约束，小众干啥的，commit才会对数据进行校验，否则立即，Update都不允许；

 

 

2：约束状态的更改：  如果对表进行数据操作，约束干扰你咋整，禁用啊；

--可以更改约束状态：

alter table a disable novalidate constraint FK_DEPTNO;   novalidate延迟，validate立即

alter table a enable validate      constraint FK_DEPTNO;   disable 禁用     ，enable 启用

 

禁用约束后，索引自动删除；

批量加载数据可以先禁用约束，提高加载数据的效率；

 

 

 

2.01  --禁用立即约束，带来的结果就是，数据可以插入更改，但是如何查询，哪些违反的数据呢？测试

--启用约束，找到引起约束失败的行；

处理大量插入数据，后启用约束失败的行；

建立一个disable  的约束；

建立表；

启动约束；

查看表；

处理违反约束条件的行；

再起启用；

 

vi /home/oracle/a2.sql               --创建一个：测试脚本

drop table a2 purge;

create table a2 as select * from scott.emp;

alter table a2 add constraint t2_pk primary key(empno) disable;

 

 

SQL> @/home/oracle/a2             --执行，创建一个表，禁用约束

--添加重复的行；

update a2 set empno=7900 where empno=7902;     --故意更改违规数据

commit;

 

--将违反约束的行放入指定表中； ----启用约束，如果不符合规范，插入异常处理表中

alter table a2 enable constraint t2_pk exceptions into exceptions ;

ERROR at line 1:

ORA-02445: Exceptions table not found

 

--执行脚本： @?/rdbms\admin\utlexcpt.sql      --创建异常表  再次执行上一条指令

 EXCEPTION选项将ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一个指定的表中。在启用约束前，硬创建一个合适的异常报告表，用来接收ENABLE子句的EXCEPTION选项信息，可以直接执行'?\rdbms\admin\utlexcpt.sql'或'?\rdbms\admin\utlexcpt1.sql'脚本来进行创建。注意：这两个脚本的区别在于数据库的兼容性级别和所分析的表的类型

 

 

ERROR at line 1:

ORA-02437: cannot validate (YANG.T2_PK) - primary key violated

SQL> select * from exceptions;        --查询异常表

 

ROW_ID             OWNER                          TABLE_NAME             CONSTRAINT

------------------ ------------------------------ ------------------------------

AAAWUqAAEAABHn7AAN YANG                           A2   T2_PK

 

AAAWUqAAEAABHn7AAM YANG                           A2  T2_PK

 

-根据数据的rowid<寻找行的数据；

SQL> select * from a2 where rowid in('AAAWUqAAEAABHn7AAN','AAAWUqAAEAABHn7AAM');

 

-更改返回

SQL> update a2 set EMPNO=7902 where ename='FORD';

-约束能启用成功，代表数据都符合了

SQL> alter table a2 enable constraint t2_pk exceptions into exceptions ;

 

SQL> commit;

-----------------------------适用于小范围修改，大范围的修改累死了，还不如删除重新修改脚本；

 

补充

===我们操作插入数据，一般针对表，那么如何查询表上的所有约束，以及约束名称呢？

 

select table_name,constraint_name,status,validated from user_constraints where table_name='A';

A                              U_EMPNO                        ENABLED       VALIDATED

A                              LI_JI                                  ENABLED       VALIDATED

 

--查询A表中的所有约束名称，没有加入，deferred判断，是立即还是延迟；

select 'alter table '||table_name||' disable validate constraint '||constraint_name||';' from user_constraints where table_name='A';

 

 

--查询A表中的所有约束状态禁用的约束；

select 'alter table '||table_name||' enable validate constraint '||constraint_name||';' from user_constraints where table_name='A' and status='DISABLED';

 

 

 

 

 

转载于:https://www.cnblogs.com/lvcha001/p/7723711.html