sqldb2database-metadatasystem-tablesrapidsql

DB2 table's UNIQUE INDEX not showing in TABCONST and CONSTDEP tables


I am trying to query the unique indices from SYSCAT tables for some user tables using the query below which uses 4 catalog tables (INDEXES, INDEXCOLUSE, TABCONST, CONSTDEP). I realized all indices are there in INDEXES and INDEXCOLUSE tables but many of them are missing in TABCONST and CONSTDEP tables.

Is there any reason for having data missing in TABCONST and CONSTDEP tables? Is there some procedure to refresh the data from those catalog tables?

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
    INNER JOIN
    SYSCAT.CONSTDEP C
    ON T.CONSTNAME = C.CONSTNAME
    INNER JOIN
    SYSCAT.INDEXES I
    ON
        C.BSCHEMA = I.INDSCHEMA AND
        C.BNAME = I.INDNAME
    INNER JOIN
    SYSCAT.INDEXCOLUSE U
    ON
        I.INDSCHEMA = U.INDSCHEMA AND
        I.INDNAME = U.INDNAME
WHERE
    TRIM(UPPER(T.TABSCHEMA)) = 'MYSCHEMA' AND
    TRIM(UPPER(T.TABNAME)) = 'MYTABLE'AND
    C.BTYPE = 'I'
--INDICES ONLY
ORDER BY
    T.TABSCHEMA,
    T.TABNAME,
    I.INDSCHEMA,
    I.INDNAME
;

Notes

Edited question's title: Forgot to mention this is all about UNIQUE index instead of just index!


Solution

  • When you create an index directly it is not a constraint:

    CREATE UNIQUE INDEX myindex on mytab(mycol)
    

    However, if you create or alter a table and use UNIQUE to declare a single column or several as such, then it is a constraint.