Steps to fix below constraint issue while doing an alter table

Please find the below as an example and see the steps for fixing.

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


@?/rdbms/admin/utlexcpt.sql

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

  • June 28, 2016 | 15 views
  • Comments