Find the row which causing violation of foreign key constraint in Oracle

Find the row which caused a violation of the foreign key when tried to enable the foreign key constraint:

To clean the rows that violate the foreign key constraint, You need to create an EXCEPTIONS table in the current schema. Use this script present in the RDBMS folder of Oracle Home: rdbms/admin/utlexcpt.sql.

Following are the steps to find the row that violate the foreign key constraint:

1. Create an exceptions table.

SQL> @?/rdbms/admin/utlexcpt.sql;

2. Populate the EXCEPTIONS table with the rows that violate the constraint, using the EXCEPTIONS INTO clause.

SQL> alter table table_name modify constraint fk_constraint_name validate exceptions into exceptions;

Note This statement while executing still throws the ORA-02298 error as long as there are rows that violate the constraint. The statement also inserts records into the EXCEPTIONS table for any bad rows.

3. Check the Exception table for rows.
   You use the ROW_ID column of the EXCEPTIONS table to remove any records that violate the constraint. In Example, you find that only one row needs to be removed from the EMP table.

 SQL> SELECT * FROM EXCEPTIONS;
Example:
ROW_ID              OWNER  TABLE_NAME  CONSTRAINT
------------------  -----  ----------  --------------------
AAAFKQAABAAAK8JAAB  SCOTT  EMP         EMP_DEPT_FK

Example of Creating Foreign key and reproducing the error and solution:

--- Created Parent p1 and Child c1 table with foreign key established 

SQL> create table p1(id number primary key, name varchar2(100));
Table created.

SQL> create table c1(cid number primary key, pid number, constraint fk_c2 foreign key (pid) references p1(id));
Table created.

-- insert into parent and child table
SQL> insert into p1 values (1,'a');
1 row created.
SQL> insert into p1 values (2,'b');
1 row created.

SQL> insert into c1 values (2,5);
insert into c2 values (2,5)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK_C2) violated - parent key not found

-- Disable the foreign key constraint to insert value
SQL> alter table c2 disable constraint FK_C2;
Table altered.

SQL> insert into c2 values (2,5);
1 row created.

--Enable the constraint throw the error 
SQL> alter table c2 enable constraint FK_C2;
alter table c2 enable constraint FK_C2
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYS.FK_C2) - parent keys not found

--Find the row causing problem to enable the constraint:
SQL> @?/rdbms/admin/utlexcpt.sql;

Table created.

--Run again the command using EXCEPTION table:
SQL> alter table c2 modify constraint FK_C2 validate exceptions into exceptions;
alter table c2 modify constraint FK_C2 validate exceptions into exceptions
                                 *
ERROR at line 1:
ORA-02298: cannot validate (SYS.FK_C2) - parent keys not found

--Check the EXCEPTION table having rowid which causing problem:
SQL> col owner for a10
SQL> col table_name for a10
SQL> col constraint for a20
SQL> select * from exceptions;

ROW_ID             OWNER      TABLE_NAME CONSTRAINT
------------------ ---------- ---------- --------------------
AAATBmAABAAAIGBAAA SYS        C2         FK_C2


-- Check the row in table 
SQL> select * from c2 where rowid in (select row_id from exceptions);

       CID        PID
---------- ----------
         2          5