Constraint Exceptions

When adding or enabling a constraint on a table, it is possible that an error occurs because of an constraint violation.  If for example you are trying to add a unique constraint to a column (or combination of columns) of a table that has duplicates (two ore more rows with the constraint columns having the same values) you will receive an error like :

ORA-02299: cannot validate (MARCEL.UN_TEST) - duplicate keys found

The error message will not tell you which rows are the cause of this error. The same goes for adding a foreign key constraint on table column (or columns) which requires an parent key to exist, you will get an error like :

ORA-02298: cannot validate (MARCEL.FK_DEP_ID) - parent keys not found

Of course you can write some query to figure out which rows are duplicate in the table or which parent keys are missing when you receive an constraint validation error when trying to add or enable a foreign key constraint, but it can be easier (and faster) to use a so called exceptions table.

When you issue a constraint command (like alter table … add constraint or alter table … enable constraint) you can specify the following clause to use this so called exception table :

alter table marcel.test add constraint un_test unique(fname,lname) exceptions into exceptions;

If for some reason the constraint command fails, the rowid for each row causing the error can be found in this exception table. Here is the definition of an exception table :

                         OWNER VARCHAR2(30),
                         TABLE_NAME VARCHAR2(30),
                         CONSTRAINT_NAME VARCHAR2(30));

For each row causing problems there will be one row in this exception table. The exception table only contains the rowid of the problem row, so you have join the exceptions table with the the table you are adding or enabling the constraint for to get the troubling rows.
Example of a query using a subquery on the exceptions table to get the “problem” rows :

select * from marcel.test where rowid in (select row_id from exceptions);

Exception table know abouts’s

  • The exceptions table can have any name you like.
    You can for example create an exceptions table named “marcelexcpt” and use this
    when specifying the exceptions clause, for example : 

    alter table test1 add constraint un_test1 unique(fname,lname) exceptions into marcelexcpt;
  • You can create a single exceptions table used for the entire database, or an table per schema. If you use a single table for the entire database you have to make sure the appropriate schema users have access to it
  • Rows causing the constraint exceptions just get inserted into a exception table. You should take care of the table purging yourself. If one exceptions table for the entire database is used, you should make sure that you only delete the rows that are generated for your own constraints. If using an schema based exceptions table just truncating the table can be enough.
  • If you want to create an exception table with the name exceptions you can run the following script to create one for you :

    Actually there are two scripts for creating an exception table, utlexcpt.sql and utlexpt1.sql. While the exceptions table created by both scripts work for “regular” tables, the exception table created by the utlexcpt.sql script will NOT work for “Index Organized Tables” and only exists for compatiblity reasons.

This entry was posted in Database and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image