ORA-02298 Validate the constraint in Oracle

Check for violate rows in the foreign key Constraint

Clean the rows that violate the constraint, For this you need to create an EXCEPTIONS table created in your currently connected schema.
Note: If you don’t have an EXCEPTIONS table, use this script present in RDBMS folder of Oracle Home : rdbms/admin/utlexcpt.sql

Follow the following steps for 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 emp modify constraint emp_dept_fk 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. 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:
 
3. Check the Exception table for rows.

SQL> select * from exceptions;

Output:

ROW_ID              OWNER  TABLE_NAME  CONSTRAINT
------------------  -----  ----------  --------------------
AAAFKQAABAAAK8JAAB  SCOTT  EMP         EMP_DEPT_FK


 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.