List table check constraints in Db2 database

Marcin Nagly - Dataedo Team Marcin Nagly 2018-07-11

Table of Contents:


    Query below lists table check constraints.

    Query

    select con.tabschema concat '.' concat con.tabname as table,
        col.colname as column_name,
        con.text  as definition,
        case tab.enforced 
          when 'N' then 'disabled'
          when 'Y' then 'active'
        end as status,
        con.constname as constraint_name
    from syscat.checks con
    join syscat.colchecks col on col.constname = con.constname 
            and col.tabschema = con.tabschema 
            and col.tabname = con.tabname
    join syscat.tabconst tab on tab.constname = con.constname 
            and tab.tabschema = con.tabschema 
            and tab.tabname = con.tabname
    where con.tabschema not like 'SYS%'
    order by con.tabschema concat '.' concat con.tabname,
        col.colname
    

    Columns

    • table - schema and table name
    • column_name - name of the column
    • definition - SQL expression that defines this check constraint
    • status - constraint status:
      • 'active' if constraint is active,
      • 'disabled' for disabled constraints
    • constraint_name - name of the constraint in the database

    Rows

    • One row represents one check constraint or column. If check constraint consists of multiple columns (table-level check constraints), each column appears separately
    • Scope of rows: query returns all check constraints in the database
    • Ordered by table schema and name, column name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept