Another foreign key question

Hello all,

   I have posted a question about finding the referenced column of a foreign key a few days ago. Someone (by Michel Cadot) posted the following SQL statement can do the task:

select cons1.table_name c1, cons1.constraint_name c2, cons2.table_name c3, col1.column_name c4, col2.column_name c5 from user_cons_columns col2, user_cons_columns col1, user_constraints cons2, user_constraints cons1
where col2.position = col1.position
and (col2.owner= cons2.owner and col2.constraint_name = cons2.constraint_name)
and (col1.owner = cons1.owner and col1.constraint_name = cons1.constraint_name)
and (cons2.owner = cons1.r_owner and cons2.constraint_name = cons1.r_constraint_name)
and cons1.constraint_type = 'R'
and cons1.table_name in ('T1','T2')
order by cons1.table_name, cons1.constraint_name, col2.position

   However, I am not fully understand the exact meaning of this statement. I don't know why it is necessary to use two same tables (col2, col1 and cons2 and cons1). Could anyone explain why it is necessary to use two same tables in this query to find the referenced column? Is it possible to find the tables which have the foreign keys reference to the current table (i.e. find the child table of the current table)?

Thanks,
David

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 28 2000 - 00:00:00 CDT