Determining Table Hierarchy

From: Jake <Im_at_nottelling.com>
Date: Mon, 23 Dec 2002 22:11:21 -0600
Message-ID: <au8n76$pbv$1@bob.news.rcn.net>

Hi,

I want to select a list of tables in order of RI. That is, I want to select all the root tables first, then the children of those tables and so on... I'm sorry if this is a frequent post, but I made various groups.google searches and found nothing.

I want to do this for various reasons, not the least is to know which tables to add data to first so as not to conflict with RI

I thought that:

  1 select table_name, max(level)
  2 from user_constraints
  3 connect by prior CONSTRAINT_NAME = R_CONSTRAINT_NAME   4* group by table_name
SQL> /

TABLE_NAME                     MAX(LEVEL)
------------------------------ ----------
CHILD                                   2
CHILD2                                  2
GRANDCHILD1                             2
GRANDCHILD2                             2
GREATGRAND                              2
PARENT                                  1

Would work but the level isn't right.

It should return:

TABLE_NAME                     MAX(LEVEL)
------------------------------ ----------
GREATGRAND                              4
GRANDCHILD1                             3
GRANDCHILD2                             3
CHILD                                   2
CHILD2                                  2
PARENT                                  1

Can someone point me in the right direction. I built a test set of tables like:

create table PARENT
(

    PARENTID          Number                 not null,

    constraint PK_PARENT primary key (PARENTID) )
/

create table CHILD
(

    CHILDID           number                 not null,
    PARENTID          Number                 null    ,

    constraint PK_CHILD primary key (CHILDID) )
/

create table CHILD2
(

    CHILDID           Number                 not null,
    PARENTID          Number                 null    ,

    constraint PK_CHILD2 primary key (CHILDID) )
/

create table GRANDCHILD1
(

    GRANDCHILDID      Number                 not null,
    CHILDID           number                 null    ,
    PARENTID          Number                 null    ,

    constraint PK_GRANDCHILD1 primary key (GRANDCHILDID) )
/

create table GRANDCHILD2
(

    GRANDCHILDID      Number                 not null,
    CHILDID           Number                 null    ,

    constraint PK_GRANDCHILD2 primary key (GRANDCHILDID) )
/

create table GREATGRAND
(

    GREATGRANDID      Number                 not null,
    GRANDCHILDID      Number                 null    ,
    CHILDID           Number                 null    ,
    GRA_GRANDCHILDID  Number                 null    ,

    constraint PK_GREATGRAND primary key (GREATGRANDID) )
/

alter table CHILD

    add constraint FK_CHILD_REF_7_PARENT foreign key (PARENTID)

       references PARENT (PARENTID)
/

alter table CHILD2

    add constraint FK_CHILD2_REF_22_PARENT foreign key (PARENTID)

       references PARENT (PARENTID)
/

alter table GRANDCHILD1

    add constraint FK_GRANDCHI_REF_13_CHILD foreign key (CHILDID)

       references CHILD (CHILDID)
/

alter table GRANDCHILD1

    add constraint FK_GRANDCHI_REF_17_PARENT foreign key (PARENTID)

       references PARENT (PARENTID)
/

alter table GRANDCHILD2

    add constraint FK_GRANDCHI_REF_27_CHILD2 foreign key (CHILDID)

       references CHILD2 (CHILDID)
/

alter table GREATGRAND

    add constraint FK_GREATGRA_REF_33_GRANDCHI foreign key (GRANDCHILDID)

       references GRANDCHILD1 (GRANDCHILDID)
/

alter table GREATGRAND

    add constraint FK_GREATGRA_REF_37_CHILD2 foreign key (CHILDID)

       references CHILD2 (CHILDID)
/

alter table GREATGRAND

    add constraint FK_GREATGRA_REF_41_GRANDCHI foreign key (GRA_GRANDCHILDID)
       references GRANDCHILD2 (GRANDCHILDID)
/

Thanks
Jake Received on Mon Dec 23 2002 - 22:11:21 CST