OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to determine the table creation order in SQL

July 28th, 2009 By Frank Zhou

The following SQL pattern can be used to determine the table creation order for a set of tables with parent-child relationship.
This SQL solution is built on top of a sql from the AskTom website, it assumes no circular dependency between the tables

create table p    ( x int primary key );
create table c1   ( x primary key references p );
create table c2   ( x primary key references c1 );
create table c3   ( x primary key references c2 );
create table c4   ( x primary key references c2 );
create table p_t  ( x int primary key );
create table c_t  ( x primary key references p_t );
create table c_t2 ( x primary key references c_t );
create table c_t3 ( x primary key references c_t2 );
create table c_t4 ( x primary key references c_t3 );
create table c_t5 ( x primary key references c_t4 );
create table m3   (x int, y int, z int );
ALTER TABLE m3 ADD (CONSTRAINT x unique  (x) );
ALTER TABLE m3 ADD (CONSTRAINT y unique  (y) );
ALTER TABLE m3 ADD (CONSTRAINT z unique  (z) );
ALTER TABLE m3 ADD (CONSTRAINT cx FOREIGN KEY (x) REFERENCES c_t5 (x));
ALTER TABLE m3 ADD (CONSTRAINT cy FOREIGN KEY (y) REFERENCES c3 (x));
ALTER TABLE m3 ADD (CONSTRAINT cz FOREIGN KEY (z) REFERENCES c4(x));

variable input varchar2(1028)
exec :input := 'P,C1,C2,C3,C4,M3,P_T,C_T,C_T2,C_T3,C_T4,C_T5'

 

————————————–SQL Solution—————————————–

With table_list AS
(
select doc.extract('/X/text( )').getStringVal() as t_name
from
TABLE(xmlSequence(extract(XMLType('<DOC><X>'||
      REGEXP_REPLACE(:input, ',', '</X><X>')||'</X></DOC>'),'/DOC/X'))) doc
)
select table_name, row_number( ) over (order by le) table_creation_order
from
(select distinct table_name, le
 from
 (select le, max(le) over (partition by table_name) max_level, table_name
  from
  (select table_name, level le
   from
   (select table_name, constraint_name pkey,
           to_char(null) fkey, to_char(null) r_con
    from   user_constraints
    where  constraint_type in ('U', 'P')
    and    owner = USER
    and table_name in (select  t_name from table_list)
    union all
    select a.table_name,
           a.constraint_name pkey,
           b.constraint_name fkey,
           b.r_constraint_name r_con
    from user_constraints a, user_constraints b
    where a.table_name = b.table_name
      and a.constraint_type in ('U', 'P')
      and b.constraint_type = 'R'
      and a.owner = USER
      and b.owner = a.owner
      and a.table_name in (select  t_name from table_list)
  )
  start with fkey is null
  connect by nocycle prior pkey = r_con
  order SIBLINGS by table_name DESC
  )
  )
  where le = max_level
);

TABLE_NAME                                               TABLE_CREATION_ORDER

——————————                        ——————–

P                                                                                  1

P_T                                                                            2

C1                                                                               3

C_T                                                                            4

C2                                                                              5

C_T2                                                                         6

C_T3                                                                         7

C3                                                                              8

C4                                                                              9

C_T4                                                                        10

C_T5                                                                        11

M3                                                                            12

12 rows selected.

One Response to “How to determine the table creation order in SQL”

  1. epipko Says:

    Hi,
    I wonder if the query provided can be used to determine the order of records deletion.
    What I mean is: it will display the table order in which you can delete records from w/out getting “… child record found” error.
    If so, is something like it available for 9.2.0.8?

    Thanks,
    Eugene

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question