OraQA

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.

Filed under General, SQL. Follow any responses to this entry through this RSS 2.0 feed.