How to find constraints columns in DB2

19,629

I'm guessing you're looking for Unique Constraints from an index. Here is a query I've used in the past to look these up for a specific table. You should be able to adapt to what you need based on what you are looking for:

SELECT
     T.TABSCHEMA AS TABLE_SCHEMA
    ,T.TABNAME   AS TABLE_NAME
    ,CASE T.TYPE
        WHEN 'F' THEN 'Foreign Key'
        WHEN 'I' THEN 'Functional Dependency'
        WHEN 'K' THEN 'Check'
        WHEN 'P' THEN 'Primary Key'
        WHEN 'U' THEN 'Unique'
     END AS Type
    ,I.INDSCHEMA AS INDEX_SCHEMA
    ,I.INDNAME   AS INDEX_NAME
    ,U.COLNAME   AS COLUMN_NAME
    ,U.COLSEQ    AS COLUMN_ORDINAL
    ,CASE U.COLORDER 
        WHEN 'A' THEN 'Ascending'
        WHEN 'D' THEN 'Descending'
        WHEN 'I' THEN 'Included (unordered)'
     END AS COLUMN_SORRING
FROM SYSCAT.TABCONST T
JOIN SYSCAT.CONSTDEP C
  ON T.CONSTNAME = C.CONSTNAME
JOIN SYSCAT.INDEXES I 
  ON C.BSCHEMA = I.INDSCHEMA
 AND C.BNAME   = I.INDNAME
JOIN SYSCAT.INDEXCOLUSE U
  ON I.INDSCHEMA = U.INDSCHEMA
 AND I.INDNAME   = U.INDNAME
WHERE T.TABSCHEMA = @schema
  AND T.TABNAME   = @table
  AND C.BTYPE     = 'I' --Indexes Only
ORDER BY 
     T.TABSCHEMA
    ,T.TABNAME
    ,I.INDSCHEMA
    ,I.INDNAME
    ,U.COLSEQ

Here are the Info Center articles for the tables involved:

SYSCAT.TABCONST

SYSCAT.CONSTDEP

SYSCAT.INDEXES

SYSCAT.INDEXCOLUSE

Share:
19,629
madarinho
Author by

madarinho

Updated on June 18, 2022

Comments

  • madarinho
    madarinho over 1 year

    I want to know the columns (names) which belong to each unique constraint. I can easily find the constraints and the tables names in syscat.tabconst. However I can't find the columns names.