Oracle違反約束數(shù)據(jù)的workaround

最近為測(cè)試做一些數(shù)據(jù)導(dǎo)入，其中存在一些主子表，由于種種原因，子表有些記錄，外鍵值在主表無記錄，導(dǎo)致數(shù)據(jù)導(dǎo)入過程中，無法創(chuàng)建外鍵，

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

此時(shí)若手工執(zhí)行，

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

就會(huì)提示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

這種數(shù)據(jù)不規(guī)則，難以滿足數(shù)據(jù)完整性要求。此時(shí)，可以使用not validate的方式，對(duì)歷史數(shù)據(jù)不進(jìn)行約束控制，而只針對(duì)新數(shù)據(jù)開啟驗(yàn)證。

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

針對(duì)正在修改的數(shù)據(jù)，以及存在的數(shù)據(jù)，可以有不同的生效設(shè)置，Oracle官方文檔的介紹，

下面可以根據(jù)rowid，定位A表違規(guī)數(shù)據(jù)，要么刪除，要么改造，讓其符合約束，進(jìn)而就可以正常執(zhí)行。

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

需要注意的是，exceptions是一張普通堆表，因此存儲(chǔ)的數(shù)據(jù)，需要自行清理，要么執(zhí)行truncate，要么執(zhí)行drop。

總結(jié)：

1. 針對(duì)不規(guī)則數(shù)據(jù)，可以使用alter table ... NOVALIDATE，對(duì)歷史數(shù)據(jù)不做約束，只約束新增數(shù)據(jù)。

2. alter table可以使用exceptions into子句，讓非法數(shù)據(jù)自動(dòng)記錄，異常表exceptions可以使用腳本，也可以自行創(chuàng)建，但需要自行清理，利用這張表可以整理數(shù)據(jù)，糾正不規(guī)則數(shù)據(jù)。