4.3 Table Relationships: One-to-Many & Referential Integrity

A well-designed database rarely stores everything in one giant table. Instead, related information is split across multiple tables that are linked together through relationships. This section covers Task Items 3.3.1 through 3.3.3 — creating a one-to-many relationship, deleting one, and applying referential integrity — using the primary key concept from the previous section as the foundation.

Why Split Data Across Related Tables?

Imagine a single table that stores every order alongside the full customer name, address, and phone number on every row. If a customer places ten orders, that customer's contact details are repeated ten times. If the customer moves house, every one of those ten rows must be updated — and if even one row is missed, the table now contains contradictory addresses for the same customer. This kind of repeated, inconsistent data is exactly what relational design avoids.

The solution is to store customers in their own table and orders in a separate table, then link the two together. Each customer's details are entered once, and each order simply references which customer placed it. This is the practical motivation behind relationships: minimizing duplication while keeping related data connected.

The One-to-Many Relationship

The most common relationship type — and the one tested directly on this exam — is the one-to-many relationship. In a one-to-many relationship:

• One record in the first table (the "one" side) can relate to many records in the second table (the "many" side).
• Each record on the "many" side relates back to only one record on the "one" side.

Example: one customer can place many orders, but each individual order belongs to exactly one customer. The Customers table is the "one" side; the Orders table is the "many" side.

How a Relationship Is Built: Primary Key to Foreign Key

A relationship connects a primary key in the "one" table to a matching field in the "many" table. That matching field, when it exists purely to reference another table's primary key, is called a foreign key. The foreign key does not need to be unique in its own table — the same CustomerID value can (and should) appear on many rows in the Orders table, once for each order that customer placed.

To create the relationship in Access, you open the Relationships window, add both tables, and drag from the primary key field in the "one" table onto the matching field in the "many" table. A relationship line then appears connecting the two tables, and a dialog lets you confirm the join and choose whether to enforce referential integrity.

Referential Integrity

Referential integrity is a set of rules that keeps related tables consistent by preventing actions that would create an orphaned record — a record on the "many" side that references a value which no longer exists on the "one" side. When referential integrity is enforced on a relationship, Access blocks:

• Adding a record on the "many" side with a foreign key value that has no matching record on the "one" side — for example, entering an order for a CustomerID that does not exist in the Customers table.
• Deleting a record on the "one" side that still has matching records on the "many" side — for example, deleting a customer who still has orders on file — unless cascading options are explicitly enabled.
• Changing the primary key value on the "one" side in a way that would break existing matches on the "many" side.

The purpose of referential integrity is straightforward for the exam: it protects the accuracy and reliability of linked data by refusing to let the two sides of a relationship fall out of sync.

Deleting a Relationship

A relationship can be removed without affecting the underlying tables or the data stored in them. To delete a one-to-many relationship, open the Relationships window, click on the relationship line connecting the two tables to select it, and delete it (via the right-click menu or the Delete key). This removes only the link and any referential integrity rule attached to it — both tables, and every record inside them, remain completely intact. This is an important distinction for the exam: deleting a relationship is not the same as deleting a table or a record.

Bringing It Together

Relationships turn a collection of separate tables into a genuine relational database. A one-to-many relationship connects a primary key on the "one" side to a matching foreign key on the "many" side, and referential integrity keeps that connection trustworthy by rejecting changes that would orphan a record. With tables designed, populated, and correctly related, the database is now ready for the next major skill area: retrieving information through searches, filters, and queries.