Oracle violates the workaround of constraint data

Recently, some data imports have been done for the test, in which there are some master child tables. Due to various reasons, there are some records in the child table, and the foreign key value is not recorded in the main table. As a result, the foreign key cannot be created during the data import process.

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

If it is executed manually at this time

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

It will prompt 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

This kind of data is irregular and difficult to meet the requirements of data integrity. At this point, you can use the not validate method to control the historical data without constraints, but only turn on verification for new data.

Alter table an add constraint fk_a_ref_b foreign key (a_id) references b (id) novalidate

There can be different effective settings for the data being modified and the existing data, as described in the official Oracle documentation.

The following can be based on the rowid, locate the A table violation data, either delete, or modify, make it meet the constraints, and then it can be executed normally.

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

It is important to note that exceptions is a normal heap table, so the stored data needs to be cleaned up by itself, either truncate or drop.

Summary:

1. For irregular data, you can use alter table... NOVALIDATE, which does not restrict historical data, but only constrains new data.

2. Alter table can use the exceptions into clause to allow illegal data to be recorded automatically. The exception table exceptions can be created by script or created by itself, but it needs to be cleaned up by itself. This table can be used to organize data and correct irregular data.