DB2 add foreign key

Parent and dependent terminology

The table that owns the referenced key is the parent table. Its primary key or eligible unique key identifies one parent row. The table containing the foreign key is the dependent table, sometimes called the child table. A department can therefore be the parent of many employees: DEPARTMENT.DEPTNO is the parent key, while EMPLOYEE.DEPTNO is the foreign key in each dependent row.

The relationship is enforced in both directions. An INSERT or UPDATE of the dependent cannot introduce a non-null key with no matching parent. A DELETE of the parent must follow the relationship's delete rule. Foreign keys do not require the values in the dependent table to be unique; many employees may belong to the same department. They enforce existence, not one-to-one cardinality.

Prerequisite 1: identify an eligible parent key

The referenced columns must already be a primary key or an eligible unique key. A nonunique index is not enough because DB2 must be able to identify one parent key unambiguously. Confirm the table schema, constraint name, key columns, and column order from the catalog rather than relying on a diagram that may be outdated.

1
2
3
4
5
6
7
8
9
10
-- Confirm the intended parent rows are unique.
SELECT DEPTNO, COUNT(*) AS ROW_COUNT
FROM   APP.DEPARTMENT
GROUP BY DEPTNO
HAVING COUNT(*) > 1;
 
-- A typical eligible parent definition:
ALTER TABLE APP.DEPARTMENT
  ADD CONSTRAINT PK_DEPARTMENT
  PRIMARY KEY (DEPTNO);

If the parent key was just added, ensure its index and table space are available before continuing. Choose stable business or surrogate columns. Referencing a value that changes frequently creates avoidable update complexity because every dependent relationship must remain valid.

Prerequisite 2: match data types and key order

Each foreign-key column must be compatible with the corresponding parent-key column. Check type family, length, precision, scale, and relevant encoding attributes. For example, DECIMAL(9,0) should not casually be paired with DECIMAL(11,2), and a character identifier should use the expected length and representation. Compatible does not mean that DB2 will silently redesign the values for you.

Order is especially important for a composite key. If the parent key is (ACCOUNT_ID, REGION_CODE), the foreign key must list the dependent account identifier first and region second. Reversing them describes a different positional mapping even when both columns happen to have compatible types.

1
2
3
4
5
ALTER TABLE APP.ORDER_HEADER
  ADD CONSTRAINT FK_ORDER_ACCOUNT_REGION
  FOREIGN KEY (ACCOUNT_ID, REGION_CODE)
  REFERENCES APP.ACCOUNT (ACCOUNT_ID, REGION_CODE)
  ON DELETE RESTRICT;

Prerequisite 3: find existing orphan rows

A table can contain years of data before a foreign key is introduced. Find rows whose non-null dependent key has no parent before executing DDL. A NOT EXISTS anti-join states the test directly and avoids the null-related surprises that can occur with NOT IN.

1
2
3
4
5
6
7
8
SELECT E.EMPNO, E.DEPTNO
FROM   APP.EMPLOYEE AS E
WHERE  E.DEPTNO IS NOT NULL
  AND NOT EXISTS
      (SELECT 1
       FROM APP.DEPARTMENT AS D
       WHERE D.DEPTNO = E.DEPTNO)
FETCH FIRST 100 ROWS ONLY;

Do not hide orphans by inserting a meaningless parent unless that row is a valid business entity. Resolve each row by creating the correct parent, correcting the dependent value, setting it to NULL when the model permits an unknown relationship, or deleting invalid data under an approved retention process. Repeat the query until it returns no rows and record the result as migration evidence.

Add the foreign key with ALTER TABLE

Give the constraint a predictable, schema-wide naming convention so messages and catalog queries are understandable. The core statement names the dependent table after ALTER TABLE, the dependent columns after FOREIGN KEY, and the parent table and key after REFERENCES.

1
2
3
4
5
ALTER TABLE APP.EMPLOYEE
  ADD CONSTRAINT FK_EMPLOYEE_DEPARTMENT
  FOREIGN KEY (DEPTNO)
  REFERENCES APP.DEPARTMENT (DEPTNO)
  ON DELETE RESTRICT;

Run DDL through the site's controlled process. Confirm the authorization ID has the required privileges, account for package and statement invalidation where applicable, avoid competing utility or DDL activity, and have a backout decision prepared. The constraint immediately becomes part of the data model; it is not merely documentation.

Choose the correct ON DELETE rule

The delete rule answers one question: what should happen when someone deletes a parent that still has dependents? It does not control deletion of a dependent row. Select the rule from business ownership and lifecycle requirements, not from convenience.

RESTRICT compared with NO ACTION

Both rules normally stop a parent delete when dependents remain, but they are not simply two spellings of the same rule. RESTRICT prevents the operation when the target parent is found to have dependents. NO ACTION requires referential integrity to be satisfied at the point DB2 performs its constraint checking, so timing and interaction with other referential actions can differ. Use the rule required by your model and test multi-table statements rather than assuming identical behavior.

CASCADE and SET NULL

CASCADE is powerful: one parent delete can remove a large dependent set and can continue through additional relationships. Use it only where children truly have no independent lifetime, and estimate logging, locking, and recovery effects. SET NULL preserves the dependent row but removes its reference. The affected foreign-key columns must support the required null result; at least one relevant foreign-key column must be nullable, and NOT NULL columns cannot be assigned NULL. Decide what a partially null composite relationship means before using it.

DB2 also restricts unsafe referential structures. Cascading cycles, conflicting delete paths, self-references, and combinations of rules may be rejected or limited. Rules vary with the exact relationship graph and Db2 level, so diagram every path from a proposed parent and check the product documentation before introducing a cascade into an existing network.

Create a useful foreign-key index

Referential integrity does not remove the need for physical design. When a parent is deleted or its key is changed, DB2 must determine whether dependent rows exist and then apply RESTRICT, NO ACTION, CASCADE, or SET NULL. Without a useful dependent-side index, that search can scan many pages, hold locks longer, and increase contention. The same index often supports joins from parent to dependent.

1
2
3
4
5
6
CREATE INDEX APP.IX_EMPLOYEE_DEPTNO
  ON APP.EMPLOYEE (DEPTNO);
 
-- For a composite relationship, preserve the useful leading order:
CREATE INDEX APP.IX_ORDER_ACCOUNT_REGION
  ON APP.ORDER_HEADER (ACCOUNT_ID, REGION_CODE);

Do not create a duplicate index automatically. An existing index can be suitable when the complete foreign key forms its leading columns, possibly followed by other columns. Review access paths, cardinality, clustering needs, insert cost, storage, and whether another index already provides the same prefix. The foreign-key index is normally nonunique because many dependents can reference one parent.

Validate data and clear restrictive states

Existing data and object state determine the operational finish. Depending on the ALTER options, current object condition, and Db2 release, DB2 can reject invalid data or leave the affected table space requiring integrity checking. Review the SQL result and object status; a successful job step alone is not enough. If the table space is in CHECK-pending, run the site-approved CHECK DATA utility process and handle exceptions rather than simply resetting the state.

1
2
3
4
5
//CHECKFK  EXEC DSNUPROC,SYSTEM=DB2A,UID='CHECKFK'
//DSNUPROC.SYSIN DD *
  CHECK DATA TABLESPACE APPDB.EMPTS
    SCOPE ALL
/*

Utility syntax and templates vary by installation, so tailor the sample through your DBA standards. CHECK DATA verifies referential and check constraints, and an exception table strategy can identify violating rows when required. After remediation, rerun validation, inspect utility return codes and messages, and confirm that the table space is available.

Verify the constraint in the DB2 catalog

Catalog verification proves that DB2 recorded the intended relationship rather than a similarly named object. Query the relationship and column catalogs using the uppercase schema and constraint values stored by ordinary identifiers. Column sequence matters for composite keys.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT CREATOR, RELNAME, TBNAME,
       REFTBCREATOR, REFTBNAME, DELETERULE
FROM   SYSIBM.SYSRELS
WHERE  CREATOR = 'APP'
  AND  TBNAME = 'EMPLOYEE'
  AND  RELNAME = 'FK_EMPLOYEE_DEPARTMENT';
 
SELECT CREATOR, TBNAME, RELNAME, COLNAME, COLSEQ
FROM   SYSIBM.SYSFOREIGNKEYS
WHERE  CREATOR = 'APP'
  AND  TBNAME = 'EMPLOYEE'
  AND  RELNAME = 'FK_EMPLOYEE_DEPARTMENT'
ORDER BY COLSEQ;

Finish with behavioral tests inside a transaction: insert a valid dependent, attempt an orphan insert and expect rejection, exercise the selected parent-delete behavior, then ROLLBACK the test changes. Use production-safe test keys and never perform destructive verification against real business rows.

Circular and self-referencing relationships

A self-referencing key, such as EMPLOYEE.MANAGER_ID referencing EMPLOYEE.EMPNO, can model a hierarchy. Make the root relationship nullable or provide another valid root design, insert managers before their reports, and understand what deleting a manager should do. A cycle across tables is harder: if A requires B and B requires A, neither initial row can be inserted unless the model permits a temporary NULL or the load process establishes the data in a supported sequence.

Circular mandatory relationships often reveal that the entities should be remodeled, one relationship should be optional, or an association table should represent the connection. Cascading deletes around a cycle can be ambiguous or destructive and are subject to DB2 restrictions. Document the whole graph, not just the one ALTER TABLE statement, and test inserts, updates, deletes, LOAD processing, recovery, and utility behavior.

Common SQL errors and corrective actions

• SQLCODE -530: an INSERT or UPDATE attempted to create a foreign-key value with no matching parent. Correct the key or create the legitimate parent first.
• SQLCODE -532: a parent DELETE or key change violated a delete rule. Delete or reassign dependents first, or revisit the modeled rule through controlled DDL.
• SQLCODE -538: the proposed foreign key does not conform to an eligible parent key or its definition. Check parent uniqueness, column count, order, and types.
• SQLCODE -539: the requested constraint conflicts with an existing constraint definition or name. Inspect the catalog before retrying.
• SQLCODE -204: DB2 could not resolve a named table or object. Check schema qualification, current SQLID, spelling, and environment.
• SQLCODE -551: the authorization ID lacks a required privilege. Obtain an approved grant or have the authorized deployment owner execute the DDL.
• SQLCODE -667 or restrictive-state messages: the object is unavailable for normal SQL because integrity checking or another utility action is required. Read the complete message and clear the state with the correct utility procedure.

Always diagnose the full SQLCA, message tokens, reason code, and Db2 message text. The negative SQLCODE identifies a category, but tokens identify the actual relationship and object. Avoid repeatedly submitting the same ALTER statement after an uncertain result; query the catalog first because the constraint may already exist.

End-to-end implementation checklist

• Confirm the parent primary or unique key and its exact column order.
• Compare every parent and dependent data type, length, precision, and scale.
• Find and remediate existing orphan rows, including composite-key cases.
• Select RESTRICT, NO ACTION, CASCADE, or SET NULL from business requirements.
• Review circular paths, cascade depth, logging, locking, and utility implications.
• Execute the named ALTER TABLE statement through controlled change management.
• Create or confirm a nonduplicate index beginning with the foreign-key columns.
• Run required validation, resolve exceptions, and clear CHECK-pending state.
• Verify SYSIBM.SYSRELS, SYSIBM.SYSFOREIGNKEYS, indexes, and object availability.
• Test valid and invalid operations and retain DDL, catalog, and utility evidence.

Explain it like I'm 5

Imagine a school has a list of classrooms, and every student card says which classroom the student belongs to. The classroom list is the parent, and the student cards are the dependents. A foreign key is a careful teacher who checks that every classroom written on a card really exists. If someone removes a classroom, the delete rule tells the teacher whether to say “stop,” remove all its student cards, or erase the classroom number while keeping the cards. An index is the teacher's alphabetized card box, which makes finding all students in one classroom much faster.

Exercises

1. Create DEPARTMENT and EMPLOYEE test tables, add a primary key to DEPARTMENT, insert valid rows, and write the anti-join that proves no orphan employee exists.
2. Add FK_EMPLOYEE_DEPARTMENT with ON DELETE RESTRICT. Attempt a valid employee insert, an orphan insert, and deletion of a referenced department. Record each SQLCODE.
3. Design a composite parent key for ACCOUNT by ACCOUNT_ID and REGION_CODE. Write the matching foreign key and explain why reversing dependent column order is incorrect.
4. Compare RESTRICT, NO ACTION, CASCADE, and SET NULL for an ORDER_HEADER and ORDER_ITEM relationship. Choose one rule and justify it from business ownership.
5. Inspect SYSIBM.SYSRELS and SYSIBM.SYSFOREIGNKEYS for your test constraint, then check whether an existing index begins with all foreign-key columns in the correct order.
6. Model an employee-manager self-reference. Define how a root employee is represented and predict the result of deleting a manager under each practical delete rule.