Db2 Relationships: One-to-One, One-to-Many, and Many-to-Many

Relationships decide how Db2 tables join.

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_PREF

The 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 CUSTOMER

A 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-NO

After 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.