Retrieving catalog information about check constraints
The SYSIBM.SYSCHECKS and SYSIBM.SYSCHECKDEP tables contain information about check constraints.
About this task
Information about check constraints is stored in the Db2 catalog in:
- SYSIBM.SYSCHECKS, which contains one row for each check constraint defined on a table
- SYSIBM.SYSCHECKDEP, which contains one row for each reference to a column in a check constraint
Procedure
To retrieve catalog information about check constraints:
Query the SYSIBM.SYSCHECKS and SYSIBM.SYSCHECKDEP tables.
The following query shows all check constraints on all tables
named SIMPDEPT and SIMPEMPL in order by column name within table schema.
It shows the name, authorization ID of the creator, and text for each
constraint. A constraint that uses more than one column name appears
more than once in the result.
CREATE TABLE SIMPDEPT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(12) CONSTRAINT CC1 CHECK (DEPTNAME IS NOT NULL),
MGRNO CHAR(6),
MGRNAME CHAR(6));SELECT A.TBOWNER, A.TBNAME, B.COLNAME,
A.CHECKNAME, A.CREATOR, A.CHECKCONDITION
FROM SYSIBM.SYSCHECKS A, SYSIBM.SYSCHECKDEP B
WHERE A.TBOWNER = B.TBOWNER
AND A.TBNAME = B.TBNAME
AND B.TBNAME = 'SIMPDEPT'
AND A.CHECKNAME = B.CHECKNAME
ORDER BY TBOWNER, TBNAME, COLNAME;