On constraint validation : use of Exception table

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