How to see the table relationships in ORACLE

oracle table-relationships
Arjun Babu picture Arjun Babu · Mar 2, 2012 · Viewed 36.5k times · Source

I had an Access database.

Now I have created a connection with this Access DB and copied it to ORACLE.

I hope the keys and constraints must be recreated in Oracle.

I couldn't find out how to create the relationships between the tables.

And also visualizing them.

Answer

Korhan Ozturk picture Korhan Ozturk · Mar 2, 2012

First of all your db keys and constraints should be maintained if you have completed migration from Access DB to Oracle successfully.

Having said that, there are products for Oracle database that will help you to visualize table relationships. There is a product called "PL/SQL Developer by Allround Automations", which we use as company, that includes this in their GUI interface - they have both "foreign keys" and "foreign key references" branches in their navigation tree for a table node (The product is not free though..). You can check here for detailed information about the 'Diagram Window' feature of PL/SQL Developer.

Secondly Oracle's SQL Developer (which is free) has a feature called 'Data Modeling' for visual representation of displaying the Relational. Check here for Oracle SQL Developer's Data Modeling.

Furthermore if you want to find out the tables that have references to a specific table you can also use the following query:

select table_name from user_constraints
where r_constraint_name in
  (select constraint_name 
     from user_constraints
     where constraint_type in ('P','U')
     and table_name = upper('&tableOfInterest')
  )

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

We have an application running locally where we're experiencing the following error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor I've tested the connection using TNSPing which resolved correctly and I tried SQLPlus to try …

Read More