Re: How to list rows violating constraints, from the exceptions table

Anne Crowther wrote:
>
> Christian Bjerregård wrote:
> >
> > Anybody out there who knows a good way to get a printout of
> > the rows in tables that violate a foreign key constraint,
> > and therefore are represented in an exceptions table with
> > it rowid.
> >
> > chbj_at_novo.dk
>
> How about:
>
> select a.*
> from tablea a, exceptions_table b
> where a.rowid = b.row_id -- must use column row_id not rowid
> -- otherwise get error
 

Your solution may be wrong. I think the answer will be as follows:

INSERT INTO Exception_Table(row_id_field)   SELECT ROWID
    FROM Child_Table
    WHERE Foreign_Key_Field IS NOT NULL AND

          NOT EXISTS (SELECT null
                        FROM Parent_Table
                        WHERE Referenced_Key_Field=Foreign_Key_Field);
          

-- 
Hui, Kwok Miu: Oracle DBA & Unix Administrator
               Cho Yang (Hong Kong) Co., Ltd.
email: huikomix_at_dt.com.hk
Tel  :(00852)2589 3369