Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
How to list tables in their dependency order (based on foreign keys)?

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
Answers
-
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
-
"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 columnCITY_ID
, primary key, and another columnCOUNTRY_ID
, foreign key pointing to a different table,COUNTRY
. TableCOUNTRY
has a columnCOUNTRY_ID
, primary key (referenced from tableCITY
and possibly from other tables as well), and another columnCAPITAL
which is a foreign key pointing to theCITY_ID
column in tableCITY
.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. -
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