How to list tables in their dependency order (based on foreign keys)?

simon87

SELECT table_name  

FROM all_Tables

WHERE OWNER = 'OWNER'

ORDER BY key_dependency;

For Example this tables:

Get list

I have this version

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE 12.1.0.2.0 Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

Frank Kulash

Hi, @simon87

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements for all tables involved, relevant columns only) so the people who want to help you can re-create the problem and test their ideas. (In this case, you might not need any INSERT statementsm; just the CREATE TABLE statements with the foreign key constraints,) Include examples of any special cases you need to handle,(such as cyclical dependencies in this problem). Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

The data dictionary views all_constraints (or user_constraints) and all_dependencies (or user_dependencies) each have the information you need. For an example, see: Primary key Foreign key — oracle-tech

mathguy

"Dependency" is not an order relation among tables. An order relation is antisymmetric: if A precedes B and also B precedes A, then A and B must be the same. This is not true for "dependency" (referential integrity): two distinct tables may depend on each other, without being the same table.

For example: Table CITY had a column CITY_ID, primary key, and another column COUNTRY_ID, foreign key pointing to a different table, COUNTRY. Table COUNTRY has a column COUNTRY_ID, primary key (referenced from table CITY and possibly from other tables as well), and another column CAPITAL which is a foreign key pointing to the CITY_ID column in table CITY.

In this very simple example, which of the two tables must be shown first in the output?

The likely reason you want to know this info is so that you can manipulate data in your tables (insert, update, delete) "in the right order". As I just argued, there is no "right order". The standard way to manipulate data in such cases is to temporarily disable the foreign key constraints, modify the data, and re-enable the constraints. With all the downsides this entails: you must lock the tables while you do this, or else you may end up with bad data, etc. Oracle has statements like insert all that allow you to do "stuff" in several tables in a single transaction, but unfortunately that won't help, because the constraints are still checked after each individual action, not at the end of the statement.

Saubhik

To get a visual representation of you relational data model, you can download https://www.oracle.com/in/database/sqldeveloper/technologies/sql-data-modeler/ and then you can reverse engineer your database https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldevdm/r40/mod1_dm_v4/mod1_dm_v4.html

This will give you a visual representation of your database with all types of relationship, constraints, indexes etc.

To query your data dictionary and get the parent child relationship long back Tom Kyte provided a query, I can't find that but this looks similar to that: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:661009003696