How to find the foreign from one table to another by Oracle?

Asked

Viewed 2,582 times

2

When we are running SQL statements and we need to navigate between tables it is simple when we know the database modeling to apply JOIN and GROUP BY, but when we do not know, what to do? It is necessary applying DESCTable in tables to know which are the foreign keys?

Is there a command in oracle that shows us the foreign keys between the tables?

that would be the way;

select f.table_name, t.table_name, t.column_name, f.constraint_name, t.owner
from all_cons_columns t, all_constraints f
where f.r_owner = t.owner
 and f.table_name = 'PARCELAMENTO'
 and f.r_owner='root';
sql oracle11c
  • 1

    http://stackoverflow.com/questions/1143728/how-can-i-find-which-tables-reference-a-given-table-in-oracle-sql-developer

1 answer

3

With this select you can see the parent and daughter tables

select f.table_name, t.table_name, t.column_name, f.constraint_name, t.owner
from all_cons_columns t, all_constraints f
where f.r_owner = t.owner
and f.r_constraint_name = t.constraint_name

If you search for a specific table you can perform can add the filter:

and  f.table_name = 'sua_tabela'

  • if I want a table specifies the end will look like this f.table_name = parceling; the table name is parceling.

  • would be: and f.table_name = 'INSTALLMENT'

  • not this sql statement because in my result returned 50 thousand records, but my table only has 6 foreign keys.

  • add to filter and f.r_owner='SEU_USUARIO'

  • take a look at my post, I just updated, and that way, if that’s the way it’s not returning me anything.

  • You need to capitalize the user name

Show 1 more comment

Browser other questions tagged sql oracle11c

You are not signed in. Login or sign up in order to post.