Mohamed Houri’s Oracle Notes

August 3, 2012

On constraint validation : use of Exception table

Filed under: Oracle — hourim @ 2:15 pm

Well, more for myself to remember if I ever need this again.Two days ago I wanted to implement a constraint on an existing table and I got this

ERROR at line 1:
ORA-02293: cannot validate (MHO.T1_N100_NR_CK) - check constraint violated

As far as I have been confirmed by the client at whom I am working that the constraint should be valid and should be implemented, I decided then to find the culprit records. How would you proceed to find those records? Well I immediately though about validating the constraint against an exception table. If you want to do it then here below are the steps to follow:

sql > create table t1
  2  as select
  3           rownum n1
  4          ,mod(rownum,100) n100
  5          ,date '2012-08-03' + (rownum-1)*5  nr
  6          ,rpad('xx',10) padding
  7  from dual
  8  connect by level  <= 1000;

Table created.

sql > update t1 set n100 = null, nr = null
  2  where n1 = 25;

1 row updated.

sql > alter table t1 add constraint t1_n100_nr_ck check (n100 is null and nr is not null
  2                                                    or n100 is not null and nr is null
  3                                                    or n100 is not null and nr is not null);
alter table t1 add constraint t1_n100_nr_ck check (n100 is null and nr is not null
                              *
ERROR at line 1:
ORA-02293: cannot validate (DXX.T1_N100_NR_CK) - check constraint violated

Well, I need to create an exception table against which I will validate the constraint so that all rejected records will be inserted into this exception table during the constraint validation

sql > create table exceptions
  2      (row_id rowid,
  3       owner varchar2(30),
  4       table_name varchar2(30),
  5       constraint varchar2(30));

Table created.

sql > alter table t1 add constraint t1_n100_nr_ck check (n100 is null and nr is not null
  2                                                    or n100 is not null and nr is null
  3                                                    or n100 is not null and nr is not null)
  4  validate exceptions into exceptions;

alter table t1 add constraint t1_n100_nr_ck check (n100 is null and nr is not null
                              *
ERROR at line 1:
ORA-02293: cannot validate (DXX.T1_N100_NR_CK) - check constraint violated

sql > select * from exceptions;

ROW_ID             OWNER      TABLE_NAME              CONSTRAINT
------------------ ----- --------------------------- ------------------
AABfJAAAuAAABCsAAY D102        T1                     T1_N100_NR_CK

So, now we have the rowid of the culprit record impeaching the constraint to be implemented

sql > select * from t1 where rowid='AABfJAAAuAAABCsAAY';

       N1       N100 NR        PADDING
--------- ---------- --------- ----------
       25        100           xx

sql > update t1 set n100 = 100 where nr is null;

1 row updated.

sql > alter table t1 add constraint t1_n100_nr_ck check (n100 is null and nr is not null
  2                                                    or n100 is not null and nr is null
  3                                                    or n100 is not null and nr is not null)
  4  validate exceptions into exceptions;

Table altered.

And this is how we are done!

Another remark in passing: you don’t need to absolutely give the name ‘exceptions’ to your exception table. You can use any name you want for that

sql > rename exceptions to my_rejected_records;

Table renamed.

sql > update t1 set n100 = null where n1 = 25;

1 row updated.

sql > alter table t1 add constraint t1_n100_nr_ck check (n100 is null and nr is not null
  2                                                    or n100 is not null and nr is null
  3                                                    or n100 is not null and nr is not null)
  4  validate exceptions into my_rejected_records;
alter table t1 add constraint t1_n100_nr_ck check (n100 is null and nr is not null
                              *
ERROR at line 1:
ORA-02293: cannot validate (DXX.T1_N100_NR_CK) - check constraint violated

sql > select count(1) from my_rejected_records;

  COUNT(1)
----------
         2
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 102 other followers

%d bloggers like this: