Oracle违反约束数据的workaround

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> Oracle违反约束数据的workaround

Oracle违反约束数据的workaround

最近为测试做一些数据导入,其中存在一些主子表,由于种种原因,子表有些记录,外键值在主表无记录,导致数据导入过程中,无法创建外键,

Failing sql is:
ALTER TABLE “A” ADD CONSTRAINT “FK_A_REF_B” FOREIGN KEY (“A_ID”) REFERENCES “B” (“ID”) ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (FK_A_REF_B) - parent keys not found

ALTER TABLE “A” ADD CONSTRAINT “FK_A_REF_B” FOREIGN KEY (“A_ID”) REFERENCES “B” (“ID”) ENABLE

ORA-02298: cannot validate (FK_A_REF_B) - parent keys not found

此时若手工执行,

ALTER TABLE A ADD CONSTRAINT FK_A_REF_B FOREIGN KEY (A_ID) REFERENCES B (ID) ENABLE;

就会提示ORA-02298,

oerr ora 2298 02298, 00000,"cannot validate (%s.%s) - parent keys not found" *Cause: an alter table validating constraint failed because the table has orphaned child records. *Action: Obvious

这种数据不规则,难以满足数据完整性要求。此时,可以使用not validate的方式,对历史数据不进行约束控制,而只针对新数据开启验证。

alter table a add constraint fk_a_ref_b foreign key(a_id) references b(id) novalidate;

针对正在修改的数据,以及存在的数据,可以有不同的生效设置,Oracle官方文档的介绍,

Oracle违反约束数据的workaround

除此之外,还有个知识点,就是使用alter出现错误的数据,是可以自动捕获,方便找出这些不规则数据,进一步手工操作。

首先需要创建一张exceptions表,有两种创建方法,

  1. Oracle提供了创建脚本。脚本路径:$ORACLE_HOME/rdbms/admin/utlexpt1.sql,

Rem Rem $Header: utlexpt1.sql 24-jun-99.07:59:18 echong Exp $ Rem Rem utlexpt1.sql Rem Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved. Rem Rem    NAME Rem      utlexpt1.sql - one-line expansion of the name Rem Rem    DESCRIPTION Rem      short description of component this file declares/defines Rem Rem    NOTES Rem      other useful comments, qualifications, etc. Rem Rem    MODIFIED   (MM/DD/YY) Rem    echong      06/24/99 - rename Rem    echong      06/05/98 - exceptions table with urowid type Rem    echong      06/05/98 - Created Rem

create table exceptions(row_id urowid,                         owner varchar2(30),                         table_name varchar2(30),                         constraint varchar2(30));

Oracle违反约束数据的workaround

执行脚本,

SQL @$ORACLE_HOME/rdbms/admin/utlexpt1.sql Table created.

SQL desc exceptions;  Name                                        Null?    Type  ----------------------------------------- -------- ----------------------------  ROW_ID                                               ROWID  OWNER                                                VARCHAR2(30)  TABLE_NAME                                      VARCHAR2(30)  CONSTRAINT                                      VARCHAR2(30)

  1. 可以不用脚本,自己创建这张表,表名可以任意。

create table exception_err(row_id urowid,                         owner varchar2(30),                         table_name varchar2(30),                         constraint varchar2(30));

使用带有exceptions into子句的alter table,

alter table A enable validate constraint FK_A_REF_B exceptions into exceptions;

此时提示ORA-02298错误,但是exceptions表已经存在数据了,

Oracle违反约束数据的workaround

下面可以根据rowid,定位A表违规数据,要么删除,要么改造,让其符合约束,进而就可以正常执行。

select * from A where rowid in (select row_id from exceptions);

需要注意的是,exceptions是一张普通堆表,因此存储的数据,需要自行清理,要么执行truncate,要么执行drop。

总结:

  1. 针对不规则数据,可以使用alter table … NOVALIDATE,对历史数据不做约束,只约束新增数据。
  1. alter table可以使用exceptions into子句,让非法数据自动记录,异常表exceptions可以使用脚本,也可以自行创建,但需要自行清理,利用这张表可以整理数据,纠正不规则数据。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

Oracle违反约束数据的workaround Oracle违反约束数据的workaround
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> Oracle违反约束数据的workaround


 上一篇
自适应log file sync影响案例 自适应log file sync影响案例
Oracle最吸引人的地方,就是有些答案,隐藏在种种现象之中,扑朔迷离,朦朦胧胧,就像侦探办案,首先要有思路,其次要有证据,再者就是扎实的基础知识,另外就是些运气。 例如最近碰见了一个案例,一套3节点11.2.0.4 RAC,某
下一篇 
Oracle导入导出的常见错误 Oracle导入导出的常见错误
做数据的导入导出操作,最常用的就是imp/exp和impdp/expdp,经常会碰见各种错误,之前总结过一些,《》、《》。 补充一些最近碰见的错误。1. exp导出提示”PLS-00302:必须说明SET_NO_OUTLINES 组件”