java oracle 约束_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，

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官方文档的介绍，

除此之外，还有个知识点，就是使用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 -

Rem

Rem    DESCRIPTION

Rem     

Rem

Rem    NOTES

Rem     

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));

执行脚本，

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)

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

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表已经存在数据了，

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

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

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

总结：

1. 针对不规则数据，可以使用alter table ... NOVALIDATE，对历史数据不做约束，只约束新增数据。

2. alter table可以使用exceptions into子句，让非法数据自动记录，异常表exceptions可以使用脚本，也可以自行创建，但需要自行清理，利用这张表可以整理数据，纠正不规则数据。

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