Find Parent Key in a constraint

Question

I am getting an exception

ORA-02291: integrity constraint (ADS.ADS_JOB_FAMILIES_F03) violated - parent key not found

I found the constraint in the table am inserting.

How to find the parent and child keys...(Columns).

Note:Am using Oracle

Are you trying to identify the name of the primary key constraint? The column(s) on which the primary key constraint is defined? Or the value of the parent key that was not found? Or are you trying to find something else? — Justin Cave, Aug 08 '12 at 16:25
Just wanted a query which wud fetch me the parent key column and foreign key column. — ashwinsakthi, Aug 09 '12 at 14:43

score 4 · Accepted Answer · edited May 23 '17 at 12:20

For the parent table:

select * from all_constraints 
         where constraint_name in (
               select R_CONSTRAINT_NAME 
                 from all_constraints 
                where constraint_name = 'ADS_JOB_FAMILIES_F03');

For the parent column:

select * 
  from all_cons_columns 
 where constraint_name in (
       select constraint_name 
         from all_constraints 
        where constraint_name in (
              select R_CONSTRAINT_NAME 
                from all_constraints 
               where constraint_name = 'ADS_JOB_FAMILIES_F03'));

This is not totally perfect because it disregards the owner of the constraints. But I think it will work for you.

Edit: I now even found this. Which would give a query like this:

SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
                        AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE a.constraint_name = 'ADS_JOB_FAMILIES_F03';

Thanks Hol..this is precisely the query i wanted. – ashwinsakthi Aug 10 '12 at 06:48 — ashwinsakthi, Aug 10 '12 at 06:48

Find Parent Key in a constraint

1 Answers1