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官方文檔的介紹，

下面可以根據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可以使用腳本，也可以自行創建，但需要自行清理，利用這張表可以整理數據，糾正不規則數據。