iRomul iRomul - 2 months ago 6
SQL Question

Select information about reference constraints

I am trying to solve the following problem (with Oracle 11g database): I need to get all table columns using

ALL_CONS_COLUMNS
table and information about reference constraints (name of table and column referenced by this column), for example:

No. Column Details
--- ----------- ------------------------------------------------------
1 foo_id Type : NUMBER(9)
Constr : "foo_fk" References foo_table(id)

2 bar_id Type : NUMBER(9)
Constr : "bar_fk" References bar_table(id)

2 date_created Type : Date


Information about table's constraints stores in
ALL_CONSTRAINTS
, detailed description of constraint (table and column name) stores in
ALL_CONS_COLUMNS
.
For now I've got this code:

SELECT
ROWNUM as "No.",
t.COLUMN_NAME as "Имя столбца",
'Type: ' || t.DATA_TYPE || (SELECT CASE
WHEN ac.CONSTRAINT_TYPE = 'R'
THEN chr(10) || 'Constr: ' || allcc.CONSTRAINT_NAME ||
' References ' || (SELECT TABLE_NAME FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) ||
'(' || (SELECT COLUMN_NAME FROM ALL_CONS_COLUMNS WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) || ')'
ELSE ''
END AS Contype FROM DUAL) As "Атрибуты",
ac.CONSTRAINT_TYPE

FROM
ALL_CONS_COLUMNS allcc

INNER JOIN
ALL_TAB_COLUMNS t
ON t.TABLE_NAME = allcc.TABLE_NAME
AND t.COLUMN_NAME = allcc.COLUMN_NAME

INNER JOIN
(SELECT (CASE WHEN CONSTRAINT_TYPE = 'R' THEN 'R' ELSE NULL END) as CONSTRAINT_TYPE, CONSTRAINT_NAME, R_CONSTRAINT_NAME FROM ALL_CONSTRAINTS) ac
ON ac.CONSTRAINT_NAME = allcc.CONSTRAINT_NAME

WHERE
t.TABLE_NAME = 'SUPPLIES';


But if column has more than 1 constraint, it will be duplicated in output for each constraint:

No. Column Details:
1 ID_SUPPLIER Type: NUMBER
2 ID_SHOP Type: NUMBER
3 DATE_SUPPLY Type: DATE
4 ID Type: NUMBER
5 ID_SHOP Type: NUMBER
Constr: FK_SUPPLIES_SHOP References SHOPS(ID)
6 ID_SUPPLIER Type: NUMBER
Constr: FK_SUPPLIES_SUPPLIER References SUPPLIERS(ID)


In this example ID_SUPPLIER duplicates twice for 2 constraints (reference and not null).
Is it possible to avoid duplicates and solve this problem without using PL\SQL?

Answer

You could use windowing functions to get rid of the duplicate rows; here, I use ROW_NUMBER():

select rownum as no,
       v1.*
  from (SELECT t.COLUMN_NAME,
               'Type:   ' || t.DATA_TYPE ||
               (SELECT CASE
                         WHEN ac.CONSTRAINT_TYPE = 'R' THEN
                          chr(10) || 'Constr: ' || allcc.CONSTRAINT_NAME || ' References ' ||
                          (SELECT TABLE_NAME
                             FROM ALL_CONS_COLUMNS
                            WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) || '(' ||
                          (SELECT COLUMN_NAME
                             FROM ALL_CONS_COLUMNS
                            WHERE CONSTRAINT_NAME = ac.R_CONSTRAINT_NAME) || ')'
                         ELSE
                          ''
                       END AS Contype
                  FROM DUAL) As description,
               ac.CONSTRAINT_TYPE,
               row_number() over(partition by allcc.column_name order by(case constraint_type
                 when 'R' then
                  1
                 else
                  2
               end)) as rn
          FROM ALL_CONS_COLUMNS allcc
         INNER JOIN ALL_TAB_COLUMNS t
            ON t.TABLE_NAME = allcc.TABLE_NAME
           AND t.COLUMN_NAME = allcc.COLUMN_NAME
         INNER JOIN (SELECT (CASE
                             WHEN CONSTRAINT_TYPE = 'R' THEN
                              'R'
                             ELSE
                              NULL
                           END) as CONSTRAINT_TYPE,
                           CONSTRAINT_NAME,
                           R_CONSTRAINT_NAME
                      FROM ALL_CONSTRAINTS) ac
            ON ac.CONSTRAINT_NAME = allcc.CONSTRAINT_NAME
         WHERE t.TABLE_NAME = 'SUPPLIES') v1
 where rn = 1;

This

  • assigns rn = 1 to each referential constraint
  • assigns rn = 1 to other constraint types if no referential constraint is present for the same column (unless there's another non-referential constraint on the column; in that case, only one of them gets rn = 1)
  • assigns rn > 1 other constraint types if a referential constraint is present
  • removes all rows whose rn > 1