How to find constraints columns in DB2

Question

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.

Estellaestelle

What constraints are you talking about? There are different types. – Oriole

You right I will edit my question – Estellaestelle

Answer 1

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

Luciusluck

There are no unique constraints without indexes. Each unique constraint is enforced by a unique index. However, the correct place to look for constraint columns (PK, unique, or FK) is SYSCAT.KEYCOLUSE. – Oriole

Answer 2

Maybe this helps. SYSCAT.COLCHECKS

Or this SYSCAT.CHECKS

Source

Figment

didn't help... these tables are empty – Estellaestelle