Oracle Live SQL - Script: Reviewed Solution

Statement 1

create table A(A_ID NUMBER(18) NOT NULL,   
               A_X_ID NUMBER(18))

Table created.

Statement 2

ALTER TABLE A ADD (  
  CONSTRAINT A_PK  
  PRIMARY KEY  
  (A_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 3

create table X(X_ID NUMBER(18) NOT NULL, X_D_ID NUMBER(18) NOT NULL)

Table created.

Statement 4

ALTER TABLE X ADD (   
  CONSTRAINT X_PK   
  PRIMARY KEY   
  (X_ID)   
  ENABLE VALIDATE)

Table altered.

Statement 5

ALTER TABLE A ADD (  
  CONSTRAINT A_X_FK   
  FOREIGN KEY (A_X_ID)   
  REFERENCES X(X_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 6

create table B(B_ID NUMBER(18) NOT NULL,  
               B_A_ID NUMBER(18) NOT NULL)

Table created.

Statement 7

ALTER TABLE B ADD (  
  CONSTRAINT B_PK  
  PRIMARY KEY  
  (B_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 8

ALTER TABLE B ADD (  
  CONSTRAINT B_A_FK   
  FOREIGN KEY (B_A_ID)   
  REFERENCES A(A_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 9

create table C(C_ID NUMBER(18) NOT NULL,  
               C_B_ID NUMBER(18) NOT NULL)

Table created.

Statement 10

ALTER TABLE C ADD (  
  CONSTRAINT C_PK  
  PRIMARY KEY  
  (C_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 11

ALTER TABLE C ADD (  
  CONSTRAINT C_B_FK   
  FOREIGN KEY (C_B_ID)   
  REFERENCES B(B_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 12

create table D(D_ID NUMBER(18) NOT NULL)

Table created.

Statement 13

ALTER TABLE D ADD (  
  CONSTRAINT D_PK  
  PRIMARY KEY  
  (D_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 14

ALTER TABLE X ADD (   
  CONSTRAINT X_D_FK    
  FOREIGN KEY (X_D_ID)    
  REFERENCES D(D_ID)   
  ENABLE VALIDATE)

Table altered.

Statement 15

create table E(E_ID NUMBER(18) NOT NULL,  
               E_A_ID NUMBER(18) NOT NULL,  
               E_D_ID NUMBER(18) NOT NULL)

Table created.

Statement 16

ALTER TABLE E ADD (  
  CONSTRAINT E_PK  
  PRIMARY KEY  
  (E_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 17

ALTER TABLE E ADD (  
  CONSTRAINT E_A_FK   
  FOREIGN KEY (E_A_ID)   
  REFERENCES A (A_ID)  
  ENABLE VALIDATE,  
  CONSTRAINT E_D_FK   
  FOREIGN KEY (E_D_ID)   
  REFERENCES D (D_ID)  
  ENABLE VALIDATE)

Table altered.

Statement 18

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as ( 
  select uc.constraint_name src,   
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,    
         uc.table_name src_tab,   
         ucc2.table_name dst_tab,   
         0 lvl ,  
         uc.constraint_type tp, 
         ucc1.column_name,  
         ucc2.column_name, 
         least(uc.table_name , ucc2.table_name) cyc_tab,  
         ucc2.table_name path 
  from   user_constraints uc   
  join   user_cons_columns ucc1  
  on     ucc1.constraint_name = uc.constraint_name 
  left join user_constraints ruc 
  on     ruc.r_constraint_name = uc.constraint_name 
  join   user_cons_columns ucc2  
  on     ucc2.constraint_name = case  
    when uc.constraint_type = 'R' then uc.r_constraint_name 
    when uc.constraint_type = 'P' then ruc.constraint_name  
  end 
  where  uc.table_name = 'D' 
  and    uc.constraint_type in ('R', 'P') 
  union all 
  select uc.constraint_name src,   
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,    
         uc.table_name src_tab,   
         ucc2.table_name dst_tab,   
         lev + 1,  
         uc.constraint_type tp, 
         ucc1.column_name,  
         ucc2.column_name, 
         least(uc.table_name , ucc2.table_name) cyc_tab,  
         path || ',' || ucc2.table_name path 
  from   constr c  
  join user_constraints uc 
  on     uc.table_name = c.dst_tab 
  and    uc.constraint_type in ('R', 'P') 
  and    uc.table_name <> 'A' 
  join   user_cons_columns ucc1  
  on     ucc1.constraint_name = uc.constraint_name 
  left join user_constraints ruc 
  on     ruc.r_constraint_name = uc.constraint_name 
  join   user_cons_columns ucc2  
  on     ucc2.constraint_name = case  
    when uc.constraint_type = 'R' then uc.r_constraint_name 
    when uc.constraint_type = 'P' then ruc.constraint_name  
  end 
) search depth first by src_tab set tab_order 
  cycle cyc_tab set cycle to 1 default 0,  
  leaves as ( 
  select c.*,  
         case when lev < lead(lev) over (order by tab_order) then null 
           else dst_tab 
         end is_leaf 
  from   constr c 
  where  cycle = 0 
), rng as ( 
  select l.*, 
         last_value(is_leaf) ignore nulls over (order by tab_order desc) lv 
  from   leaves l 
) 
  select * from rng 
  where  lv = 'A' 
  order  by lev


SRC DST SRC_TAB DST_TAB LEV TP COL1 COL2 CYC_TAB PATH TAB_ORDER CYCLE IS_LEAF LV
D_PK X_D_FK D X 0 P D_ID X_D_ID D X 4 0  - A
D_PK E_D_FK D E 0 P D_ID E_D_ID D E 1 0  - A
X_PK A_X_FK X A 1 P X_ID A_X_ID A X,A 6 0 A A
E_A_FK A_PK E A 1 R E_A_ID A_ID A E,A 2 0 A A

4 rows selected.

SRC	DST	SRC_TAB	DST_TAB	LEV	TP	COL1	COL2	CYC_TAB	PATH	TAB_ORDER	IS_LEAF	LV
D_PK	X_D_FK	D	X	0	P	D_ID	X_D_ID	D	X	4	-	A
D_PK	E_D_FK	D	E	0	P	D_ID	E_D_ID	D	E	1	-	A
X_PK	A_X_FK	X	A	1	P	X_ID	A_X_ID	A	X,A	6	A	A
E_A_FK	A_PK	E	A	1	R	E_A_ID	A_ID	A	E,A	2	A	A

Statement 19

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as ( 
  select uc.constraint_name src,   
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,    
         uc.table_name src_tab,   
         ucc2.table_name dst_tab,   
         0 lvl ,  
         uc.constraint_type tp, 
         ucc1.column_name,  
         ucc2.column_name, 
         least(uc.table_name , ucc2.table_name) cyc_tab,  
         ucc2.table_name path 
  from   user_constraints uc   
  join   user_cons_columns ucc1  
  on     ucc1.constraint_name = uc.constraint_name 
  left join user_constraints ruc 
  on     ruc.r_constraint_name = uc.constraint_name 
  join   user_cons_columns ucc2  
  on     ucc2.constraint_name = case  
    when uc.constraint_type = 'R' then uc.r_constraint_name 
    when uc.constraint_type = 'P' then ruc.constraint_name  
  end 
  where  uc.table_name = 'D' 
  and    uc.constraint_type in ('R', 'P') 
  union all 
  select uc.constraint_name src,   
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,    
         uc.table_name src_tab,   
         ucc2.table_name dst_tab,   
         lev + 1,  
         uc.constraint_type tp, 
         ucc1.column_name,  
         ucc2.column_name, 
         least(uc.table_name , ucc2.table_name) cyc_tab,  
         path || ',' || ucc2.table_name path 
  from   constr c  
  join user_constraints uc 
  on     uc.table_name = c.dst_tab 
  and    uc.constraint_type = 'R' 
  and    uc.table_name <> 'A' 
  join   user_cons_columns ucc1  
  on     ucc1.constraint_name = uc.constraint_name 
  left join user_constraints ruc 
  on     ruc.r_constraint_name = uc.constraint_name 
  join   user_cons_columns ucc2  
  on     ucc2.constraint_name = case  
    when uc.constraint_type = 'R' then uc.r_constraint_name 
    when uc.constraint_type = 'P' then ruc.constraint_name  
  end 
) search depth first by src_tab set tab_order 
  cycle cyc_tab set cycle to 1 default 0,  
  leaves as ( 
  select c.*,  
         case when lev < lead(lev) over (order by tab_order) then null 
           else dst_tab 
         end is_leaf 
  from   constr c 
  where  cycle = 0 
), rng as ( 
  select l.*, 
         last_value(is_leaf) ignore nulls over (order by tab_order desc) lv 
  from   leaves l 
) 
  select * from rng 
  where  lv = 'A' 
  order  by lev


SRC DST SRC_TAB DST_TAB LEV TP COL1 COL2 CYC_TAB PATH TAB_ORDER CYCLE IS_LEAF LV
D_PK E_D_FK D E 0 P D_ID E_D_ID D E 1 0  - A
E_A_FK A_PK E A 1 R E_A_ID A_ID A E,A 2 0 A A

2 rows selected.

SRC	DST	SRC_TAB	DST_TAB	LEV	TP	COL1	COL2	CYC_TAB	PATH	TAB_ORDER	CYCLE	IS_LEAF	LV
D_PK	E_D_FK	D	E	0	P	D_ID	E_D_ID	D	E	1	0	-	A
E_A_FK	A_PK	E	A	1	R	E_A_ID	A_ID	A	E,A	2	0	A	A

Statement 20

with constr ( src, dst, src_tab, dst_tab, lev, tp, col1, col2, cyc_tab, path) as ( 
  select uc.constraint_name src,   
         nvl( ruc.constraint_name , uc.r_constraint_name)  dst,    
         uc.table_name src_tab,   
         ucc2.table_name dst_tab,   
         0 lvl ,  
         uc.constraint_type tp, 
         ucc1.column_name,  
         ucc2.column_name, 
         least(uc.table_name , ucc2.table_name) cyc_tab,  
         ucc2.table_name path 
  from   user_constraints uc   
  join   user_cons_columns ucc1  
  on     ucc1.constraint_name = uc.constraint_name 
  left join user_constraints ruc 
  on     ruc.r_constraint_name = uc.constraint_name 
  join user_tab_cols utc1  
  on utc1.table_name = ucc1.table_name 
  and utc1.column_name = ucc1.column_name 
  join   user_cons_columns ucc2  
  on     ucc2.constraint_name = case  
    when uc.constraint_type = 'R' then uc.r_constraint_name 
    when uc.constraint_type = 'P' then ruc.constraint_name  
  end 
  join user_tab_cols utc2  
  on utc2.table_name = ucc2.table_name 
  and utc2.column_name = ucc2.column_name 
  where  uc.table_name = 'D' 
  and    uc.constraint_type in ('R', 'P') 
  and utc1.nullable = 'N' 
  and utc2.nullable = 'N' 
  union all 
  select uc.constraint_name src,   
         nvl( ruc.constraint_name , uc.r_constraint_name) dst,    
         uc.table_name src_tab,   
         ucc2.table_name dst_tab,   
         lev + 1,  
         uc.constraint_type tp, 
         ucc1.column_name,  
         ucc2.column_name, 
         least(uc.table_name , ucc2.table_name) cyc_tab,  
         path || ',' || ucc2.table_name path 
  from   constr c  
  join user_constraints uc 
  on     uc.table_name = c.dst_tab 
  and    uc.constraint_type in ('R', 'P') 
  and    uc.table_name <> 'A' 
  join   user_cons_columns ucc1  
  on     ucc1.constraint_name = uc.constraint_name 
  join user_tab_cols utc1  
  on utc1.table_name = ucc1.table_name 
  and utc1.column_name = ucc1.column_name 
  left join user_constraints ruc 
  on     ruc.r_constraint_name = uc.constraint_name 
  join   user_cons_columns ucc2  
  on     ucc2.constraint_name = case  
    when uc.constraint_type = 'R' then uc.r_constraint_name 
    when uc.constraint_type = 'P' then ruc.constraint_name  
  end 
  join user_tab_cols utc2  
  on utc2.table_name = ucc2.table_name 
  and utc2.column_name = ucc2.column_name 
  where utc1.nullable = 'N' 
  and utc2.nullable = 'N' 
) search depth first by src_tab set tab_order 
  cycle cyc_tab set cycle to 1 default 0,  
  leaves as ( 
  select c.*,  
         case when lev < lead(lev) over (order by tab_order) then null 
           else dst_tab 
         end is_leaf 
  from   constr c 
  where  cycle = 0 
), rng as ( 
  select l.*, 
         last_value(is_leaf) ignore nulls over (order by tab_order desc) lv 
  from   leaves l 
) 
  select * from rng 
  where  lv = 'A' 
  order  by lev


SRC DST SRC_TAB DST_TAB LEV TP COL1 COL2 CYC_TAB PATH TAB_ORDER CYCLE IS_LEAF LV
D_PK E_D_FK D E 0 P D_ID E_D_ID D E 1 0  - A
E_A_FK A_PK E A 1 R E_A_ID A_ID A E,A 2 0 A A

2 rows selected.

Reviewed Solution

Additional Information