A check constraint is a rule enforced by DB2 that specifies the values allowed in one or more columns of every row of a table. Prior to DB2 V4.1, check constraints could be specified on views, but their usefulness was quite limited.

Check constraints are optional. They are defined at the time a table is created, or can be added or modified later with the ALTER TABLE statement.

DB2 enforces constraints when:

• A row is inserted into the table.
• A row in the table is updated.
• Data is added to the table using the LOAD utility with the ENFORCE CONSTRAINTS option.

When a constraint is added via ALTER. the tablespace is placed in CHECK PENDING status:



The CHECK PENDING status is reset by running the CHECK utility:



A -545 SQLCODE is returned if an operation violates a check constraint:



If, prior to adding a constraint via ALTER, the special register CURRENT RULES is set to 'STD' (instead of its default value, 'DB2'), the constraint will be checked immediately and, if no errors are found, applied immediately to the table. If errors are found, the constraint definition fails, and the table definition is unchanged.



Finally, information on constraints is recorded in the SYSIBM.SYSCHECKS and SYSIBM.SYSCHECKDEP catalog tables, whence it can be retrieved via SQL, or presented in a more comprehensible format by the various catalog visibility tools such as Platinum.