DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, June 28, 2016

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

No comments: