Check and other constraints in DB2

The constraint family

Primary key, unique, and foreign key were the previous page’s RI story. They still belong here because CHECK and NOT NULL sit beside them on CREATE TABLE. A solid table definition usually mixes several kinds.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE HR.EMPLOYEE (
  EMPNO    CHAR(6)      NOT NULL,
  LASTNAME VARCHAR(15)  NOT NULL,
  WORKDEPT CHAR(3),
  SALARY   DECIMAL(9,2),
  BONUS    DECIMAL(9,2),
  COMM     DECIMAL(9,2),
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
  CONSTRAINT FK_DEPT FOREIGN KEY (WORKDEPT)
    REFERENCES HR.DEPARTMENT (DEPTNO)
    ON DELETE SET NULL,
  CONSTRAINT CK_SAL CHECK (SALARY IS NULL OR SALARY >= 0),
  CONSTRAINT CK_PAY CHECK (SALARY IS NULL OR BONUS IS NULL
                       OR SALARY + BONUS >= 0)
) IN HRDB.EMPTS;

NOT NULL

NOT NULL is specified on the column, not as CONSTRAINT … CHECK (COL IS NOT NULL)—though that CHECK would be similar. Primary key columns must be NOT NULL. Foreign keys may be nullable (SET NULL needs that). Unique constraints can allow nulls depending on UNIQUE versus UNIQUE WHERE NOT NULL on the supporting index.

NOT NULL is enforced on INSERT, UPDATE, and LOAD. SQLCODE -407 is the classic “null into NOT NULL column” error. Defaults (WITH DEFAULT) are how you keep NOT NULL without forcing every INSERT list to mention the column.

PRIMARY KEY and UNIQUE

PRIMARY KEY names the main identifier. There is one primary key per table. It implies NOT NULL and a unique index. UNIQUE constraints are additional uniqueness rules (email, national id). Both are parent keys that a FOREIGN KEY may reference. Dropping a unique index that supports a constraint is not allowed while the constraint exists.

Constraint dependencies: a FOREIGN KEY depends on the parent PRIMARY/UNIQUE constraint and its index. DROP TABLE on the parent fails while dependents exist. DROP CONSTRAINT on the child FK first, or DROP the child table.

CHECK constraints

A CHECK constraint is a search condition. Each row must not make it FALSE. If a column is null, comparisons are UNKNOWN, and UNKNOWN does not fail CHECK. That surprises people who wrote CHECK (SALARY > 0) and still inserted null salaries. Write CHECK (SALARY IS NOT NULL AND SALARY > 0) or keep SALARY NOT NULL.

1
2
3
4
5
6
7
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT CK_STATUS
    CHECK (STATUS IN ('A', 'I', 'P'));
 
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT CK_DATES
    CHECK (HIREDATE IS NULL OR HIREDATE <= CURRENT DATE);

CHECK conditions on z/OS are restricted: they refer to columns of the same row, not to other tables (that is RI or a trigger). They are not a substitute for FOREIGN KEY. Keep expressions sargable and cheap; they run on every write to the involved columns.

SQLCODE -545 is a typical check-constraint violation. The constraint name in the message is why you wrote CK_STATUS instead of a generated name.

Constraint names, ownership, dependencies

CONSTRAINT name is an ordinary identifier. It must be unique among constraints of that table (and follows schema uniqueness rules for the catalog). The table owner owns the constraints. You do not GRANT on a constraint separately; you ALTER the table.

1
ALTER TABLE HR.EMPLOYEE DROP CONSTRAINT CK_STATUS;

Dependencies: check constraints depend on the columns they name. Dropping a column may require dropping the check first (or the DROP COLUMN rules reject it). Referential constraints depend on parent keys. Triggers are separate objects but often assume constraints already hold.

Catalog: SYSIBM.SYSTABCONST, SYSCHECKS, SYSKEYCOLUSE, SYSRELS, SYSFOREIGNKEYS. Query them when a DROP fails with “dependent objects.”

Enforcement, validation, and checking

Enforced constraints (the default) are validated on INSERT, UPDATE, MERGE, and LOAD ENFORCE CONSTRAINTS. The statement fails and the row is not stored if the rule is FALSE. All constraints that apply to the statement are considered; Db2 rolls the statement back on error (the triggering DML and its statement-level work).

Validation of existing data happens when you ADD a constraint to a populated table. If Db2 cannot be sure the old rows pass, the table space goes CHECK-pending. That is z/OS “deferred constraint processing”: the definition is in the catalog, but SQL that requires a clean space waits until CHECK DATA proves the rows. CHECK DATA also checks RI (enforced) and can DELETE violating rows to an exception table.

Db2 for z/OS does not implement Oracle-style DEFERRABLE INITIALLY DEFERRED constraints that wait until COMMIT. Do not copy that syntax. If you need to load in an order that temporarily breaks RI, use LOAD ENFORCE NO, then CHECK DATA, or insert null FKs and UPDATE after parents exist.

Referential integrity (in the constraint set)

FOREIGN KEY is a constraint. Delete rules (RESTRICT, NO ACTION, CASCADE, SET NULL) are part of its definition. See the referential integrity page for cycles, self-references, LOAD, and recovery. Here, remember: CHECK cannot replace FK. CHECK (WORKDEPT IN ('A00','B01')) is a closed list; FK (WORKDEPT) REFERENCES DEPARTMENT tracks a living parent table.

Informational constraints

NOT ENFORCED on a referential (or check) constraint tells Db2: trust the application; do not validate on DML; do not have CHECK DATA verify it. The optimizer may still use the relationship—especially automatic query rewrite with materialized query tables that join extra parent tables.

1
2
3
4
5
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT FK_DEPT_INFO
    FOREIGN KEY (WORKDEPT)
    REFERENCES HR.DEPARTMENT (DEPTNO)
    NOT ENFORCED;

Use informational RI only when another process truly guarantees keys (a trusted ETL, an application server that never lets SPUFI in). If someone can DELETE a department in SPUFI, you wanted ENFORCED. Informational constraints that lie cause wrong MQT rewrites—wrong answers, not just wrong speeds.

CHECK DATA does not check informational referential constraints. DISPLAY CHKP will not save you if the only FK was NOT ENFORCED.

Choosing CHECK versus trigger versus application

Use CHECK for a predicate on the same row (status in a list, amount >= 0, date order). Use FOREIGN KEY for “must exist in another table.” Use a trigger when the rule needs other tables’ current values, emails, or SEQUENCE logic CHECK cannot express. Use application code only as a supplement: the database rule is the last line of defense.

Too many overlapping CHECKs slow writes. One CHECK with AND is usually cheaper than five tiny constraints, but five named constraints give better SQLCODEs. Balance message quality against maintenance.

After you ADD CONSTRAINT, regenerate DCLGEN only if columns changed; constraints do not change FETCH layouts. They do change which INSERT values survive. Add tests that try illegal values and expect -545 or -530.

Check-constraint search conditions on Db2 for z/OS are limited on purpose. They refer to columns of the same table and the same row. They do not SELECT from another table (that is a foreign key or a trigger). They do not call most user-defined functions or column functions. CURRENT DATE and similar special registers can appear in some predicates (hire date not in the future) but make the rule time-dependent: a row that passed yesterday can fail CHECK DATA tomorrow if you wrote an absolute comparison carelessly. Prefer predicates that stay true as the clock moves, unless the business rule is explicitly “not after today.”

Adding a CHECK to a populated table is the same CHKP story as adding a FOREIGN KEY: Db2 records the constraint, then you run CHECK DATA (or the ADD can succeed immediately if Db2 can prove existing rows already satisfy it, which is uncommon for a non-trivial predicate). Do not ADD CONSTRAINT in the same change-window as a 20-million-row LOAD unless you planned ENFORCE NO plus a later CHECK DATA that has exception tables and enough work-file space.

Constraint names collide inside a table. Two CHECKs cannot share CK_SAL. Schema-level uniqueness rules also apply for some object types—if CREATE fails with a duplicate name, pick a name that includes the table: CK_EMP_SAL, CK_EMP_STATUS. Generated names (SYSnnnnnn) are legal and hostile in operations. Every constraint you care about should appear in source-controlled DDL with an explicit CONSTRAINT clause.

Ownership follows the table. If HR owns EMPLOYEE, HR owns CK_SAL. Revoking a personal TSO id that created the table in its own schema is a mess; create production tables under a controlled owner. You GRANT ALTER on the table to the team that must ADD or DROP constraints. There is no GRANT CHECK TO PUBLIC. Informational NOT ENFORCED constraints are still owned objects: DROP them explicitly when the MQT they supported is gone, so the catalog does not lie forever.

Validation versus enforcement is the CHKP distinction again. Enforcement is the INSERT/UPDATE/LOAD path. Validation is CHECK DATA (and, for some adds, the CREATE/ALTER itself). A space in CHKP can restrict SQL until you validate. DISPLAY DATABASE … SPACENAM … RESTRICT shows CHKP. Do not confuse it with COPY-pending (image copy) or REORG-pending. Clearing CHKP without CHECK DATA (for example a misguided REPAIR) can leave orphans that SQL will not catch until the next write.

Explain It Like I'm Five

Constraints are playground rules written on the fence so every game uses them. NOT NULL means “you must pick a number.” PRIMARY KEY means “two kids cannot wear the same jersey.” FOREIGN KEY means “your team letter must match a real team.” CHECK means “your score cannot be negative.” NOT ENFORCED is a rule the coach swears the kids already follow, so the referee does not blow the whistle—but the scoreboard may still assume it is true. CHECK-pending is “we wrote a new rule and have not inspected the old games yet.”

Exercises

1. Write a CHECK that STATUS is only 'A' or 'I'.
2. Explain why CHECK (SALARY > 0) still allows a null SALARY.
3. DROP CONSTRAINT for a check named CK_STATUS.
4. When would you define FOREIGN KEY … NOT ENFORCED?
5. Name the utility that validates enforced checks and RI after CHKP.