List all check constraints in Db2 database

Query below lists check constraints defined in the database ordered by constraint name.

Check this query to see them organized by table.

Query

select con.constname as constraint_name,
    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
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.constname

Columns

  • constraint_name - name of the constraint in the database
  • table - schema and table name constraint is defined for
  • 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

Rows

  • One row represents one check constraint or constraint 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 constraint name

Sample results

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