Steps to fix below constraint issue while doing an alter table
Failing sql is:
ALTER TABLE “ABC”.”DEF” ADD CONSTRAINT “G_490” FOREIGN KEY (“ORDERS_ID”) REFERENCES “ABC”.”ORDERS” (“ORDERS_ID”) ON DELETE CASCADE ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (ABC.G_520) – parent keys not found
1) Run the below script under the schema. this will create exceptions table under that schema
2) Then run the alter table enable constraints command (see below example), suffix exceptions into exceptions at the end of the alter statement.
ALTER TABLE “ABC”.”DEF” ADD CONSTRAINT “H_915” FOREIGN KEY (“JOB”) REFERENCES “ABC”.”DEF” (“JOB”) ON DELETE CASCADE ENABLE exceptions into exceptions;
3) The above statement will capture violating rows into exceptions table.
You can query the exceptions table to find the violating rows
select * from exceptions;
The output will give the rowid of the violating rows.
You can delete the rows from the child table. delete from child table where rowid IN (select row_id from exceptions)
Truncate the exceptions table and repeat the same for all the constraints.
Truncate table exceptions