2.4 Relationships & Referential Integrity (Concepts)

The final concept-level building block in this chapter is the relationship — the mechanism that connects separate tables so a database can avoid retyping the same data over and over. This section covers ICDL skill set 1.3, items 1.3.1 through 1.3.3, at a concept level; the hands-on task of creating relationships in a table is covered later, in section 4.3.

Why Relationships Exist: Minimising Duplication (1.3.1)

Recall from section 2.2 that a well-designed database splits data into separate, single-subject tables — for example, a Customers table and a separate Orders table, rather than repeating each customer's full details on every order row. Splitting the data this way is only useful if the tables can still be reconnected when needed. That reconnection is exactly what a relationship provides.

By relating tables instead of duplicating data, a database:

• Stores each fact — such as a customer's address — only once, in the Customers table.
• Lets any number of Orders records reference that customer without copying the address into every order.
• Makes updates simpler and safer: change the customer's address once, and every related order automatically reflects the correct, current address.

How a Relationship Is Built: Primary Key to Foreign Key (1.3.2)

A relationship works by matching a unique field in one table to a corresponding field in another table. The unique field on the "one" side is the table's primary key (see section 2.3) — for example, CustomerID in the Customers table. The matching field on the related table is called a foreign key — for example, an Orders table would include its own CustomerID field, which does not have to be unique there (many orders can share the same customer), but which must match a real CustomerID value that exists in the Customers table.

The ICDL syllabus recognises three general types of relationship between tables:

For this module, the one-to-many relationship is the one you will actually build and manage (covered fully in section 4.3), since it is by far the most common pattern in everyday databases. A one-to-one relationship is comparatively rare in practice, because if every record in Table A only ever relates to exactly one record in Table B, the two tables can often simply be combined into one. A many-to-many relationship cannot be created directly between two tables in Access-based systems; it is normally resolved by introducing a third, linking table that holds a foreign key pointing to each of the two original tables — for example, a StudentCourses table with one record for every student/course combination.

Why Maintaining Integrity Matters (1.3.3)

Once tables are related, the database needs a way to make sure those links stay valid and consistent — this is called referential integrity. Referential integrity is a set of rules that prevents actions which would break a relationship, such as:

• Adding an order for a CustomerID that does not exist in the Customers table (an "orphan" record with no valid parent).
• Deleting a customer while related orders for that customer still exist, without first dealing with those related records.

When referential integrity is enforced, the database actively blocks these inconsistent situations, protecting the accuracy of the data. Without it, a database can silently accumulate broken links — orders that point to customers who were deleted, or records that no longer make logical sense — which undermines the entire purpose of using related tables in the first place.

Referential integrity is what makes the earlier goal of minimising duplication actually trustworthy in daily use. It is not enough to simply link two tables through a shared key; the database also has to guarantee that every link it stores genuinely points to something real. That guarantee is exactly what turns a relationship from a convenient shortcut into a dependable source of accurate information for forms, queries, and reports built later in this guide.

Bringing Chapter 2 Together

Across this chapter you have covered the full conceptual foundation of the ICDL Using Databases module: what a database is and how it differs from a spreadsheet (2.1); how tables, records, and fields are organised around one subject, one instance, and one data element (2.2); how data types, field properties, primary keys, and indexes control what a field can store and how quickly it can be found (2.3); and now, how relationships and referential integrity connect separate tables while keeping duplication low and data trustworthy (2.4). These four concepts reappear constantly through the rest of the guide — especially in chapter 4 (Tables), where you will build tables, set primary keys, and create real one-to-many relationships yourself. Keep one mental picture: the primary key on the "one" side is copied into a matching foreign key column on the "many" side, and referential integrity is the rule that stops that foreign key from ever pointing to a parent record that does not exist. Master that single sentence and the exam's relationship questions become straightforward.