Referential integrity in DB2 for z/OS

Parent keys: PRIMARY KEY and UNIQUE

The parent table holds the values that children are allowed to copy. Those values must be unique. You declare a PRIMARY KEY (columns NOT NULL plus a unique index) or a UNIQUE constraint (unique index; columns may be nullable unless you also say NOT NULL). A foreign key can reference either. If you omit the parent column list on REFERENCES, Db2 uses the primary key.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE HR.DEPARTMENT (
  DEPTNO   CHAR(3)  NOT NULL,
  DEPTNAME VARCHAR(36) NOT NULL,
  MGRNO    CHAR(6),
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
) IN HRDB.DEPTTS;
 
CREATE TABLE HR.EMPLOYEE (
  EMPNO    CHAR(6) NOT NULL,
  LASTNAME VARCHAR(15) NOT NULL,
  WORKDEPT CHAR(3),
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
) IN HRDB.EMPTS;

You need a unique index on the parent key before (or as part of) defining the foreign key. Privileges: the definer needs ALTER or REFERENCES on the parent key columns.

FOREIGN KEY, parent table, dependent table

A foreign key is a column or set of columns in the dependent table that references the parent key. Name the constraint so DROP CONSTRAINT and error messages are readable.

1
2
3
4
5
ALTER TABLE HR.EMPLOYEE
  ADD CONSTRAINT FK_WORKDEPT
    FOREIGN KEY (WORKDEPT)
    REFERENCES HR.DEPARTMENT (DEPTNO)
    ON DELETE SET NULL;

Insert rule: a nonnull foreign key must match some parent key. SQLCODE -530 is the usual failure. Update rule: same test when you change the foreign key or the parent key. A composite foreign key is treated as null if any component is null—so (DEPT, NULL) does not have to match a parent.

To make a table self-referencing, create the table first, then ALTER TABLE ADD FOREIGN KEY. You cannot point at a parent key that does not exist yet on the same CREATE in every case; adding the FK afterward is the supported pattern (for example EMP.MGRNO referencing EMP.EMPNO).

Delete rules

1
2
3
4
5
6
7
8
9
10
11
12
-- Reject delete of a department that still has employees
FOREIGN KEY (WORKDEPT) REFERENCES HR.DEPARTMENT(DEPTNO)
  ON DELETE RESTRICT
 
-- Or check after AFTER triggers have run
  ON DELETE NO ACTION
 
-- Fire employees when the department is deleted (dangerous)
  ON DELETE CASCADE
 
-- Keep employees, blank their department
  ON DELETE SET NULL

RESTRICT and NO ACTION both raise an error (often SQLCODE -532) and delete nothing from the parent if dependents remain. They differ in when Db2 looks. RESTRICT is enforced as the parent row is deleted, before AFTER triggers. NO ACTION is enforced at the end of the statement. An AFTER DELETE trigger that removes dependents can make NO ACTION succeed where RESTRICT would have failed. For a simple DELETE with no triggers they behave the same.

CASCADE deletes dependents. If a dependent is also a parent, its delete rule runs too. A CASCADE that reaches a RESTRICT/NO ACTION descendant fails the whole statement. Plan the graph on paper before you enable CASCADE in production.

SET NULL is allowed only if some FK column is nullable. NOT NULL foreign keys cannot use SET NULL. Self-referencing delete rules have extra restrictions—read the SQL Reference before EMP.MGRNO ON DELETE CASCADE on EMPLOYEE.

Db2 does not enforce multiple constraints in a documented fixed order. That is why some CREATE/ALTER combinations of delete rules are rejected: the result would depend on luck.

Referential cycles and self-referencing RI

A self-referencing table is parent and dependent of itself (manager works in the same employee table). A cycle is A references B and B references A (or a longer loop). Cycles are legal with care: you often INSERT parents with null FKs, then UPDATE the foreign keys, or you insert in an order that never points at a missing parent. DROP TABLE of a cycle needs the foreign keys dropped first.

Self-referencing ON DELETE CASCADE can try to delete the row that is both parent and child. IBM documents specific delete-rule limits for self-referencing tables. Prefer RESTRICT or application-controlled deletes until you have tested the cascade on a copy.

RI enforcement, violations, and CHECK DATA

Db2 enforces RI when you INSERT into a dependent, UPDATE parent or dependent keys, DELETE from a parent, run LOAD on a dependent with ENFORCE CONSTRAINTS, or run the CHECK DATA utility. It does not enforce NOT ENFORCED (informational) constraints on those paths.

Adding a FOREIGN KEY to a table that already has rows places the table space in CHECK-pending (CHKP). SQL that depends on proven RI is restricted until you validate. CHECK DATA examines referential and table check constraints. SCOPE PENDING checks only CHKP objects. DELETE YES can remove violating rows (to an exception table if you define one); DELETE NO reports them. Informational constraints are skipped.

Typical violation: orphan WORKDEPT 'Z99' with no DEPARTMENT row. CHECK DATA reports it; you INSERT the department, UPDATE the employee, or DELETE the orphan.

RI and LOAD, REORG, recovery

LOAD ENFORCE CONSTRAINTS checks RI as rows go in (or at the end of the load, depending on options). ENFORCE NO is faster and leaves CHKP so you can LOAD parent and child in any order, then CHECK DATA once. The usual pattern: load parents first with ENFORCE, then dependents with ENFORCE, or load all with ENFORCE NO and CHECK DATA.

REORG does not invent orphan keys. It reorganizes pages. After REORG, RI is as valid as it was before. Do not skip CHECK DATA after a load just because a REORG is scheduled.

Recovery of a parent table space to a different point in time than its dependents can create orphans or missing parents. After a PIT recovery of related spaces, run CHECK DATA on the dependents. Recover related table spaces to the same RBA/LRSN when you can. Image copies of parent and child should be thought of as a set.

RI performance

Every INSERT into a dependent probes the parent unique index. Every DELETE of a parent must find dependents. Without an index on the foreign key, that search is a table scan. IBM recommends an index on the FK when parent deletes (or parent-key updates) are common. CASCADE multiplies that work down the tree.

Informational RI (NOT ENFORCED) exists so the optimizer can use relationships (especially with materialized query tables) when applications already enforce keys. Do not mark a constraint NOT ENFORCED to “make LOAD faster” if you still need Db2 to reject orphans—use ENFORCE NO plus CHECK DATA instead.

SQLCODE -530 (insert/update) and -532 (delete) belong in application handlers. Retrying without fixing the key does not help. Display the constraint name from the message; it is why you named FK_WORKDEPT instead of accepting SYS123456.

Package invalidation and extra index maintenance are the cost of declarative RI. The benefit is that every path—SPUFI, QMF, COBOL, LOAD—obeys the same rule. Application-only RI fails the first time someone runs an ad-hoc DELETE.

Updating a parent key is rare on purpose. Db2 must find every dependent that still points at the old value and either reject the update (the usual update rule is RESTRICT) or, if you designed otherwise, rewrite those foreign keys. Shops almost always treat parent keys as immutable identifiers: assign DEPTNO once, never recycle it. If you must rename a department, INSERT the new parent, UPDATE dependents, then DELETE the old parent under RESTRICT once no children remain.

Catalog tables that document the graph include SYSIBM.SYSRELS (each referential constraint, delete rule, parent table), SYSFOREIGNKEYS (FK columns), SYSKEYCOLUSE (parent key columns), and SYSTABCONST. When a -530 or -532 message names a generated constraint, query those tables. When DROP TABLE says dependents exist, the same catalog rows tell you which child to ALTER first.

In data sharing, RI checks still use the parent unique index and the dependent FK index. Extra GBP traffic appears when many members insert children of the same hot parent row (the parent index leaf is a hotspot). That is not a reason to drop RI; it is a reason to avoid a single “catch-all” parent key that every insert touches, and to size the group buffer pool for the parent unique index.

A table can be both parent and dependent of several relationships at once. EMPLOYEE might reference DEPARTMENT, PROJECT, and EMP (manager). Each relationship has its own delete rule. A DELETE FROM DEPARTMENT with CASCADE can fire a chain that hits a RESTRICT on another relationship and fail. Draw every FOREIGN KEY on one diagram before you enable CASCADE in production. Test the DELETE of a leaf parent, a middle parent, and a parent with mixed rules.

Explain It Like I'm Five

A department is a house. An employee’s WORKDEPT is the house number written on their backpack. Referential integrity says you cannot write a house number that is not on any house. If you tear the house down, RESTRICT means “stop, kids still live there,” CASCADE means “the kids go with the house,” and SET NULL means “erase the house number on the backpacks.” CHECK DATA is the teacher walking the playground to find backpacks with fake numbers. LOAD with ENFORCE is checking numbers as kids get off the bus; ENFORCE NO is letting everyone off and checking later.

Exercises

1. Write a FOREIGN KEY from EMPLOYEE.WORKDEPT to DEPARTMENT.DEPTNO with ON DELETE SET NULL.
2. Explain when you would choose RESTRICT instead of CASCADE.
3. Why must SET NULL have a nullable foreign-key column?
4. Describe why adding a FK to a full table sets CHKP and which utility clears it.
5. Give one reason to index WORKDEPT on EMPLOYEE.