recursive subquery

View All Public Scripts Login to Run Script

Script Name recursive subquery
Visibility Unlisted - anyone with the share link can access
Description Retrieving all table parent-child dependencies with recursive subquery
Area SQL General / SQL Query
Contributor xmafia@gmx.de
Created Monday August 07, 2017

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,
  CONSTRAINT X_D_FK  
  FOREIGN KEY (X_D_ID)  
  REFERENCES D(D_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

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 15

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

Table altered.

Statement 16

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 17

insert into x values (1)

1 row(s) inserted.

Statement 18

insert into x values (2)

1 row(s) inserted.

Statement 19

insert into a values (1,1)

1 row(s) inserted.

Statement 20

insert into a values (2,1)

1 row(s) inserted.

Statement 21

insert into a values (3,2)

1 row(s) inserted.

Statement 22

insert into b values (1,1)

1 row(s) inserted.

Statement 23

insert into b values (2,2)

1 row(s) inserted.

Statement 24

insert into c values (1,1)

1 row(s) inserted.

Statement 25

insert into c values (2,2)

1 row(s) inserted.

Statement 26

insert into d values (1)

1 row(s) inserted.

Statement 27

insert into d values (2)

1 row(s) inserted.

Statement 28

insert into e values (1,1,1)

1 row(s) inserted.

Statement 29

insert into e values (2,1,2)

1 row(s) inserted.

Statement 30

insert into e values (3,2,1)

1 row(s) inserted.

Statement 31

insert into e values (4,2,2)

1 row(s) inserted.

Statement 32
```
commit
```
```
Statement processed.
```

Statement 33

with CONSTR(NAME_CURR,  
         NAME_RECUR,  
         CONST_TYPE,  
         TBL,  
         LVL)  
     AS (SELECT UC.CONSTRAINT_NAME name_curr,  
     case UC.CONSTRAINT_TYPE when 'P' then uc.constraint_name when 'R' then UC.R_CONSTRAINT_NAME end NAME_RECUR,  
      uc.constraint_type CONST_TYPE,  
      UC.TABLE_NAME TBL,  
      1 LVL  
      from USER_CONSTRAINTS uc  
     where uc.table_name = 'C'  
     and UC.CONSTRAINT_TYPE in ('P','R')  
     union all  
      SELECT  CASE CONSTR.CONST_TYPE WHEN 'R' THEN UC.CONSTRAINT_NAME ELSE UC.R_CONSTRAINT_NAME END NAME_CURR,  
              CASE UC.CONSTRAINT_TYPE WHEN 'R' THEN UC.R_CONSTRAINT_NAME ELSE UC.CONSTRAINT_NAME END NAME_RECUR,  
              UC.CONSTRAINT_TYPE CONST_TYPE,  
                UC.TABLE_NAME TBL,  
                CONSTR.LVL + 1  
           FROM CONSTR  
                JOIN USER_CONSTRAINTS UC  
                   ON (UC.CONSTRAINT_NAME = CONSTR.NAME_RECUR AND UC.R_CONSTRAINT_NAME != CONSTR.NAME_CURR AND UC.CONSTRAINT_TYPE = 'P')  
                   OR (UC.CONSTRAINT_NAME = CONSTR.NAME_CURR AND UC.CONSTRAINT_NAME != CONSTR.NAME_RECUR AND UC.CONSTRAINT_TYPE = 'P')  
                   OR (UC.R_CONSTRAINT_NAME = CONSTR.NAME_CURR AND UC.CONSTRAINT_TYPE = 'R')  
                   )  
           SEARCH BREADTH FIRST BY NAME_CURR, NAME_RECUR SET ORDERING  
           CYCLE NAME_CURR SET cycle TO 1 DEFAULT 0  
  SELECT NAME_CURR,  
         NAME_RECUR,  
         TBL,  
         LVL, 
         UCC1.TABLE_NAME, 
         UCC1.COLUMN_NAME, 
         UCC2.TABLE_NAME, 
         UCC2.COLUMN_NAME 
    FROM CONSTR  
    JOIN USER_CONS_COLUMNS UCC1 on UCC1.CONSTRAINT_NAME = NAME_CURR 
    JOIN USER_CONS_COLUMNS UCC2 on UCC2.CONSTRAINT_NAME = NAME_RECUR 
ORDER BY ORDERING


NAME_CURR NAME_RECUR TBL LVL TABLE_NAME COLUMN_NAME TABLE_NAME COLUMN_NAME
C_B_FK B_PK C 1 C C_B_ID B B_ID
C_PK C_PK C 1 C C_ID C C_ID

2 rows selected.

NAME_CURR	NAME_RECUR	TBL	LVL	TABLE_NAME	COLUMN_NAME	TABLE_NAME	COLUMN_NAME
C_B_FK	B_PK	C	1	C	C_B_ID	B	B_ID
C_PK	C_PK	C	1	C	C_ID	C	C_ID

Statement 34

with CONSTR(NAME_CURR,   
         NAME_RECUR,   
         CONST_TYPE,   
         TBL,   
         LVL)   
     AS (SELECT UC.CONSTRAINT_NAME name_curr,   
     case UC.CONSTRAINT_TYPE when 'P' then uc.constraint_name when 'R' then UC.R_CONSTRAINT_NAME end NAME_RECUR,   
      uc.constraint_type CONST_TYPE,   
      UC.TABLE_NAME TBL,   
      1 LVL   
      from USER_CONSTRAINTS uc   
     where uc.table_name = 'D'   
     and UC.CONSTRAINT_TYPE in ('P','R')   
     union all   
      SELECT  CASE CONSTR.CONST_TYPE WHEN 'R' THEN UC.CONSTRAINT_NAME ELSE UC.R_CONSTRAINT_NAME END NAME_CURR,   
              CASE UC.CONSTRAINT_TYPE WHEN 'R' THEN UC.R_CONSTRAINT_NAME ELSE UC.CONSTRAINT_NAME END NAME_RECUR,   
              UC.CONSTRAINT_TYPE CONST_TYPE,   
                UC.TABLE_NAME TBL,   
                CONSTR.LVL + 1   
           FROM CONSTR   
                JOIN USER_CONSTRAINTS UC   
                   ON (UC.CONSTRAINT_NAME = CONSTR.NAME_RECUR AND UC.R_CONSTRAINT_NAME != CONSTR.NAME_CURR AND UC.CONSTRAINT_TYPE = 'P')   
                   OR (UC.CONSTRAINT_NAME = CONSTR.NAME_CURR AND UC.CONSTRAINT_NAME != CONSTR.NAME_RECUR AND UC.CONSTRAINT_TYPE = 'P')   
                   OR (UC.R_CONSTRAINT_NAME = CONSTR.NAME_CURR AND UC.CONSTRAINT_TYPE = 'R')   
                   )   
           SEARCH BREADTH FIRST BY NAME_CURR, NAME_RECUR SET ORDERING   
           CYCLE NAME_CURR SET cycle TO 1 DEFAULT 0   
  SELECT NAME_CURR,   
         NAME_RECUR,   
         TBL,   
         LVL,  
         UCC1.TABLE_NAME,  
         UCC1.COLUMN_NAME,  
         UCC2.TABLE_NAME,  
         UCC2.COLUMN_NAME  
    FROM CONSTR   
    JOIN USER_CONS_COLUMNS UCC1 on UCC1.CONSTRAINT_NAME = NAME_CURR  
    JOIN USER_CONS_COLUMNS UCC2 on UCC2.CONSTRAINT_NAME = NAME_RECUR  
ORDER BY ORDERING


NAME_CURR NAME_RECUR TBL LVL TABLE_NAME COLUMN_NAME TABLE_NAME COLUMN_NAME
D_PK D_PK D 1 D D_ID D D_ID
D_PK D_PK E 2 D D_ID D D_ID

2 rows selected.

NAME_CURR	NAME_RECUR	TBL	LVL	TABLE_NAME	COLUMN_NAME	TABLE_NAME	COLUMN_NAME
D_PK	D_PK	D	1	D	D_ID	D	D_ID
D_PK	D_PK	E	2	D	D_ID	D	D_ID

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database