Tuesday, February 16, 2010

Foreign Key Constraints Referencing to a Table

We had a table to purge with primary key.
Problem is we need to backup purged data which includes data from all the child tables referencing with foreign keys to that table.
Now I had to find all the child tables and their foreign key columns. I used below query to resolve this problem:

select a.owner,
a.constraint_name,
a.constraint_type,
a.table_name,
b.column_name,
a.r_owner,
a.r_constraint_name
from dba_constraints a, dba_cons_columns b
where a.owner = 'MYUSER'
and a.owner = b.owner
and a.constraint_name = b.constraint_name
and a.r_constraint_name in
(select constraint_name
from dba_constraints
where table_name = 'MYTABLE'
and owner = 'MYUSER');


http://bytes.com/topic/oracle/answers/644008-query-find-primary-foreign-keys
http://www.databasejournal.com/features/oracle/article.php/3665591/Finding-Foreign-Key-Constraints-in-Oracle.htm

1 comment:

  1. very useful in real life scenario. Good job man !!!

    ReplyDelete