A COBOL cursor that joins CUSTOMER to ACCOUNT is only as good as the table relationship behind it. If ACCOUNT.CUST_NO does not point back to a real customer key, the program can return missing rows, duplicate rows, or orphan account records that no business user can explain.
This refresh explains the three relationship patterns used in relational database design: one-to-one, one-to-many, and many-to-many. The examples use Db2 table names, primary keys, foreign keys, and embedded SQL patterns that a mainframe developer is likely to review in batch or CICS programs.
What a Db2 Relationship Means
A relationship exists when rows in one table are associated with rows in another table. In Db2, that association is usually expressed through key columns: a primary key or unique key on the parent table, and a foreign key on the child table.
CREATE TABLE CUSTOMER
(CUST_NO CHAR(10) NOT NULL,
CUST_NAME VARCHAR(60),
PRIMARY KEY (CUST_NO));
CREATE TABLE ACCOUNT
(ACCT_NO CHAR(12) NOT NULL,
CUST_NO CHAR(10) NOT NULL,
BALANCE DECIMAL(13,2),
PRIMARY KEY (ACCT_NO),
FOREIGN KEY (CUST_NO)
REFERENCES CUSTOMER (CUST_NO));In this example, CUSTOMER is the parent table and ACCOUNT is the child table. The relationship says that an account row must reference an existing customer row.
Primary Key, Foreign Key, and Parent Table
| Term | Meaning in Db2 design | Example |
|---|---|---|
| Primary key | Column or columns that uniquely identify a row in a table. | CUSTOMER.CUST_NO |
| Foreign key | Column or columns in one table that reference a parent key. | ACCOUNT.CUST_NO |
| Parent table | Table that owns the referenced key. | CUSTOMER |
| Child table | Table that stores the foreign key. | ACCOUNT |
| Referential integrity | Rule that keeps child rows from pointing to missing parent rows. | Account cannot reference an unknown customer. |
One-to-One Relationship
A one-to-one relationship means one row in the first table matches at most one row in the second table, and one row in the second table matches at most one row in the first table.
A common design is a base table plus a detail table:
CUSTOMER
CUST_NO primary key
CUST_NAME
CUSTOMER_PROFILE
CUST_NO primary key and foreign key
TAX_STATUS
CONTACT_PREFThe detail table uses the same key as the parent. This pattern is useful when optional or sensitive columns should be stored separately, but the row still belongs to exactly one parent row.
One-to-Many Relationship
A one-to-many relationship means one parent row can have many child rows, while each child row points to one parent row. This is the most common relationship in business applications.
CUSTOMER
CUST_NO primary key
ACCOUNT
ACCT_NO primary key
CUST_NO foreign key to CUSTOMERA COBOL program might fetch all accounts for one customer like this:
EXEC SQL
DECLARE C-ACCT CURSOR FOR
SELECT A.ACCT_NO,
A.BALANCE
FROM ACCOUNT A
WHERE A.CUST_NO = :WS-CUST-NO
END-EXEC.If CUST_NO is indexed on ACCOUNT, this kind of cursor is easier for Db2 to access efficiently.
Many-to-Many Relationship
A many-to-many relationship means rows on both sides can match many rows on the other side. Do not store repeating columns such as COURSE_1, COURSE_2, and COURSE_3. Use a linking table, also called an associative or junction table.
STUDENT
STUD_ID primary key
COURSE
COURSE_ID primary key
ENROLLMENT
STUD_ID foreign key to STUDENT
COURSE_ID foreign key to COURSE
ENROLL_DATE
PRIMARY KEY (STUD_ID, COURSE_ID)The linking table turns one many-to-many relationship into two one-to-many relationships. That keeps inserts, deletes, and joins easier to control.
Join Pattern for a Linking Table
A COBOL report that lists courses for one student can join through the linking table:
EXEC SQL
DECLARE C-COURSE CURSOR FOR
SELECT C.COURSE_ID,
C.COURSE_TITLE,
E.ENROLL_DATE
FROM ENROLLMENT E
JOIN COURSE C
ON C.COURSE_ID = E.COURSE_ID
WHERE E.STUD_ID = :WS-STUD-ID
ORDER BY E.ENROLL_DATE
END-EXEC.The cursor does not need repeating host variables for course slots. Each enrollment row is one fact.
Relationship Cardinality at a Glance
| Relationship | Parent and child pattern | Db2 design note |
|---|---|---|
| One-to-one | One parent row maps to one detail row. | The child key often acts as both primary key and foreign key. |
| One-to-many | One parent row maps to many child rows. | The foreign key belongs on the many side. |
| Many-to-many | Many rows on each side can match. | Use a linking table with foreign keys to both parent tables. |
Delete Rules Need Care
Relationships affect delete and update behavior. If a customer row has account rows, Db2 must know what should happen when someone tries to delete the customer.
| Referential action | Typical meaning | Production caution |
|---|---|---|
RESTRICT or NO ACTION | Prevent parent delete when child rows exist. | Common for master data that must not be removed while dependent rows remain. |
CASCADE | Delete child rows when the parent is deleted. | Use only when the business rule is explicit and tested. |
SET NULL | Set the child foreign key to null when the parent is deleted. | Only works when a missing parent is valid for the application. |
Batch purge programs need special care here. A delete that looks small in the parent table can affect many child rows.
Common Design Mistakes
- Putting the foreign key on the wrong side of a one-to-many relationship.
- Modeling many-to-many relationships with repeating columns instead of a linking table.
- Using nullable foreign keys when the business rule requires a parent row.
- Skipping indexes on foreign key columns that are frequently joined or searched.
- Writing joins from column names alone without checking the real relationship.
How Relationships Affect Db2 Performance
Relationships are logical design, but they also affect access paths. A cursor that joins parent and child tables usually needs useful indexes on join columns and current statistics.
SELECT C.CUST_NAME,
A.ACCT_NO,
A.BALANCE
FROM CUSTOMER C
JOIN ACCOUNT A
ON A.CUST_NO = C.CUST_NO
WHERE C.CUST_NO = :WS-CUST-NOAfter the relationship is correct, use EXPLAIN and statistics to review the access path. The Db2 SQL Optimization Tips for COBOL Programs post covers that check.
How This Fits with Other Db2 Topics
Relationships are part of relational design. They sit close to Db2 Anatomy of a Relational Database, Db2 Objects, and Db2 Indexing. For current product context, see IBM's Db2 for z/OS page and the ISO SQL framework.
FAQ
What is a one-to-many relationship in Db2?
A one-to-many relationship means one parent row can match many child rows. The child table stores a foreign key that points to the parent key.
How do I model a many-to-many relationship?
Use a linking table. The linking table stores foreign keys to both parent tables and often uses those columns as a composite primary key.
Do foreign keys improve query performance?
Foreign keys define the relationship and protect data consistency. Performance usually depends on indexes, statistics, predicates, and the access path Db2 chooses.
Good relationship design gives COBOL SQL a clean target: one parent key, clear child rows, and joins that match the business rule.
