Oracle : display foreign key columns-CodePudding

I have the following query which works fine and produces these results.

Where I'm stuck is trying to figure out the column(s) that are used to join the foreign keys.

In the test case below, it should be in column (id) but if it's a compound key I like to have a comma separated list.

I would like to add this new information after the F_KEY column. Any help would be greatly appreciated.

My test case:

CREATE TABLE parent 
(
    id NUMBER(10),
    value VARCHAR2(30),

    CONSTRAINT parent_pk PRIMARY KEY (id)
);

CREATE TABLE child
( 
    id NUMBER(10) NOT NULL,
    value NUMBER(10) NOT NULL,

    CONSTRAINT child_pk PRIMARY KEY (id, value),
    CONSTRAINT parent_child_fk
        FOREIGN KEY (id) REFERENCES parent(id)
                ON DELETE CASCADE
);

CREATE TABLE grandchild
( 
    id NUMBER(10) NOT NULL,
    value NUMBER(10) NOT NULL,

    CONSTRAINT grandchild_pk PRIMARY KEY (id, value),
    CONSTRAINT child_grandchild_fk
        FOREIGN KEY (id, value) REFERENCES child (id, value)
                ON DELETE CASCADE
);

WITH f AS
(
    SELECT
        constraint_name, table_name, r_constraint_name
    FROM
        user_constraints
    WHERE
        constraint_type = 'R'
),
p AS 
(
    SELECT
        constraint_name, table_name
    FROM
        user_constraints
    WHERE
        constraint_type = 'P'
),
j (child_table, f_key, parent_table, p_key) AS 
(
    SELECT
        f.table_name, f.constraint_name, p.table_name, f.r_constraint_name
    FROM
        p 
    JOIN
        f ON p.constraint_name = f.r_constraint_name
    UNION ALL
    SELECT
        'PARENT', 
        (SELECT constraint_name 
         FROM p
         WHERE table_name = 'PARENT'), NULL, NULL
    FROM dual
)
SELECT
    level AS lvl, j.*
FROM
    j
START WITH parent_table IS NULL
CONNECT BY nocycle parent_table = prior child_table
ORDER BY lvl, parent_table, child_table;

Current output

LVL   CHILD_TABLE  F_KEY                PARENT_TABLE    P_KEY
------------------------------------------------------------------
 1    PARENT       PARENT_PK             -               - 
 2    CHILD        PARENT_CHILD_FK       PARENT         PARENT_PK
 3    GRANDCHILD   CHILD_GRANDCHILD_FK   CHILD          CHILD_PK

Desired output

LVL   CHILD_TABLE  F_KEY              F_COL   PARENT_TABLE    P_KEY
------------------------------------------------------------------------
 1    PARENT       PARENT_PK            -         -             -
 2    CHILD        PARENT_CHILD_FK      ID    PARENT         PARENT_PK
 3    GRANDCHILD   CHILD_GRANDCHILD_FK  ID    CHILD          CHILD_PK

CodePudding user response：

You can use user_cons_columns dictionary view joining with the query j and then LISTAGG() through aggregation in order to concat multiple columns such as

WITH f AS
(
    SELECT constraint_name, table_name, r_constraint_name
      FROM user_constraints
     WHERE constraint_type = 'R'
), p AS 
(
    SELECT constraint_name, table_name
      FROM user_constraints
     WHERE constraint_type = 'P'
),j (child_table, f_key, column_name, parent_table, p_key) AS 
(
    SELECT f.table_name, f.constraint_name, c.column_name, 
           p.table_name, f.r_constraint_name
      FROM p
      JOIN f
        ON p.constraint_name = f.r_constraint_name
      LEFT JOIN user_cons_columns c
        ON c.constraint_name = f.constraint_name
    UNION ALL
    SELECT 'PARENT', 
           (SELECT constraint_name 
              FROM p
             WHERE table_name = 'PARENT'), NULL, NULL, NULL
      FROM dual
)
 SELECT MAX(level) AS lvl, child_table, f_key, 
        LISTAGG(column_name,',') WITHIN GROUP (ORDER BY column_name) AS f_key, 
        parent_table, p_key
   FROM j
  START WITH parent_table IS NULL
CONNECT BY nocycle parent_table = prior child_table
GROUP BY child_table, f_key, parent_table, p_key
ORDER BY lvl, parent_table, child_table

Demo