Foreign Key

Enhanced Definition

A `FOREIGN KEY` is a column or a set of columns in a DB2 for z/OS table that refers to the `PRIMARY KEY` (or a `UNIQUE KEY`) in the same table or another table. It establishes a **referential constraint** between the two tables, ensuring data integrity by enforcing predefined relationships.

Key Characteristics

- Referential Integrity: Enforces rules to maintain consistency between related tables, preventing the creation of orphaned child records or deletion of parent records that still have dependent children.
- Parent/Child Relationship: The table containing the PRIMARY KEY is designated as the parent table, while the table with the FOREIGN KEY is the child table.
- Matching Data Types: The FOREIGN KEY columns must have data types and lengths compatible with the PRIMARY KEY columns they reference to allow for proper comparison and linking.
- NULL Values: A FOREIGN KEY can contain NULL values, indicating that a child record is not currently related to any parent record, unless the column is explicitly defined as NOT NULL.
- DB2 Implementation: In DB2 for z/OS, FOREIGN KEY constraints are defined using CREATE TABLE or ALTER TABLE DDL statements, specifying the referenced table and columns.
- Referential Actions: DB2 allows specifying actions to take when a parent row is deleted or updated, such as CASCADE (delete/update child rows), SET NULL (set child FK to NULL), RESTRICT (prevent parent action), or NO ACTION (defer integrity check).

Use Cases

- Order Management System: Linking an Orders table (child) to a Customers table (parent) using CustomerID as the FOREIGN KEY to ensure every order placed belongs to an existing customer record.
- Inventory Tracking: Connecting an Inventory table to a Products table, where ProductID in Inventory is a FOREIGN KEY referencing ProductID in Products, ensuring only valid products are inventoried.
- Hierarchical Data Representation: Modeling organizational structures where an Employee table might have a FOREIGN KEY ManagerID referencing EmployeeID within the same table (a self-referencing foreign key).
- Financial Transaction Systems: Linking Transaction_Details records to a Master_Account record, where AccountID in the details table is a FOREIGN KEY to the master table.

Related Concepts

FOREIGN KEYs are fundamental to relational database management systems (RDBMS) like DB2 for z/OS, working in conjunction with PRIMARY KEYs and UNIQUE KEYs to enforce referential integrity. They are a core component of data modeling and database design, ensuring that relationships between entities are correctly maintained and that orphaned records cannot exist. This data consistency is crucial for applications written in COBOL, accessed via CICS transactions, or processed through JCL batch jobs, as it guarantees the reliability of data retrieved and updated.

Best Practices:

Define Explicitly: Always define FOREIGN KEY constraints explicitly in your CREATE TABLE or ALTER TABLE DDL to leverage DB2's built-in integrity enforcement mechanisms.
Choose Appropriate Referential Actions: Carefully select ON DELETE and ON UPDATE actions (CASCADE, SET NULL, RESTRICT, NO ACTION) based on the specific business rules to prevent unintended data loss or inconsistencies.
Index Foreign Keys: Create indexes on FOREIGN KEY columns, especially if they are frequently used in JOIN operations or as part of WHERE clauses, to significantly improve query performance.
Document Relationships: Clearly document all PRIMARY KEY and FOREIGN KEY relationships in your database schema for better understanding, maintenance, and troubleshooting by developers and DBAs.
Monitor Constraint Violations: Implement monitoring for SQLCODEs related to constraint violations (e.g., -530 for referential integrity violation) to identify and address data integrity issues promptly.