Constraints
Constraints on the IBM i platform are rules defined on database tables (both DDL and DDS) to enforce data integrity by restricting the types of data that can be inserted, deleted, or updated.
This document outlines the types, benefits, and operation of the named constraints in DB2 as implemented on IBM i.
Named constraints are those that are explicitly given a unique identifier within the database, allowing easier management and clearer error reporting.
Types of DB2 Named Constraints on IBM i
CONSTRAINT DESCRIPTION
Primary Key : Uniquely identifies each record in a table. Cannot be NULL
Unique: Ensures all values in the specified column(s) are unique.
Referential: Enforces a relationship between two dependent tables.
Check: Ensures column values satisfy a defined condition/expression.
Primary Key Constraints
- Enforce the uniqueness of one or more columns to identify each row uniquely.
- No columns defined in the Primary Key can be NULL.
- Only one primary key constraint can be added to an existing table.
- Existing data is checked for uniqueness before the constraint is applied.
- Eliminates the possibility of duplicate records (rows) in the table.
Unique Constraints
- Ensures that all values in the constrained columns are unique across the table.
- Can be added to existing tables with a unique constraint name.
- Existing data is checked for uniqueness before the constraint is applied.
Referential Constraints (Foreign Key Constraints)
- Define relationships between tables by requiring that values in one or more foreign key columns (child) correspond to values in primary or unique key columns of another table.
- Enforced during data modification operations like INSERT, UPDATE, or DELETE.
- Existing data is checked for the relationship before the constraint is applied.
- Both parent and dependent tables must reside in the same auxiliary storage pool (ASP).
Check Constraints
- Specify a Boolean expression that must be true for data in a row.
- Can be applied to one or multiple columns.
- Violations prevent data modification and raise specific error statuses during I/O operations.
Naming and Management of Constraints
- Constraint names must be unique within the table or library and distinct from other constraints.
- Named constraints appear in error messages when violated, aiding in troubleshooting.
- Constraint names are also used in both O/S and SQL management commands.
- Adopting naming conventions is recommended to help identify the type and purpose of constraints more easily.
Benefits of Using Constraints
- Embeds the control of uniqueness and relationships into the database as a single point of control.
- Some limited degree of data validation can be embedded with the use of CHECK constraints.
- Eliminates the need for multiple application programs to perform the same validations multiple times. This simplifies the application code and logic.
- Improves performance by reducing the number of I/O operations in the applications.
- Constraint violations result in standard DB2 error messages, making problems easier to identify and debug.
Best Practices
- Always define a PRIMARY KEY for every table.
- Use “Referential Constraints” to document and enforce parent-child relationships to prevent orphaned records.
- Adopt a standardized naming convention for constraints.
Conclusion
Constraints on IBM i are explicit, uniquely identified rules applied to tables to centralize and enforce data integrity. They include primary key, unique, referential, and check constraints. Naming constraints facilitate management, error handling, and modification. Constraints ensure that data adheres to the defined business rules and relationships, preventing invalid data entry and maintaining database consistency.
I want to acknowledge my colleague, Tommy Atkins, for his guidance and assistance with this article.
.
