Advanced Module – Databases (Access)

Lesson 3 – Relationships and Referential Integrity

This lesson explains how to create, edit and manage relationships between tables, enforce referential integrity, and understand join types used in Access queries. These skills ensure data consistency and support accurate relational database design.

1. Types of relationships

• One-to-many – one record in Table A links to many in Table B (e.g. one Customer has many Orders).
• One-to-one – rare; one record in each table matches a single record in another table.
• Many-to-many – implemented using a junction table (e.g. a student can enrol on many courses, and a course can have many students).

2. Opening the Relationships window

1. Open your database.
2. Go to the Database Tools tab.
3. Click Relationships.
4. Add tables using Show Table if they are not displayed.

3. Creating one-to-many relationships

1. In the Relationships window, drag the primary key field (e.g. CustomerID in Customers) onto the foreign key field (e.g. CustomerID in Orders).
2. The Edit Relationships dialog appears.
3. Tick Enforce Referential Integrity.
4. Optionally tick:
   • Cascade Update Related Fields
   • Cascade Delete Related Records (use with care)
5. Click Create.

Result: A “1” symbol appears on the one side and an “∞” (infinity) symbol on the many side.

4. Referential integrity

Referential integrity ensures that relationships between tables remain valid at all times.

• You cannot add a child record linked to a non-existent parent.
• You cannot delete a parent record if related child records exist (unless cascade delete is used).
• You cannot change a key value in the parent unless related records are updated (cascade update).

When to use cascade options

• Cascade Update – used when primary key values may change (rare for AutoNumber keys).
• Cascade Delete – use with extreme caution, as all related child records will be removed automatically.

5. Join types in relationships and queries

Joins control which records appear when tables are used together in a query.

• Inner join – shows only matching records from both tables.
• Left join (Left Outer) – shows all records from the left table and matching records from the right.
• Right join (Right Outer) – shows all records from the right table and matching records from the left.

How to change a join type in a query

1. Open the query in Design View.
2. Double-click the join line between two tables.
3. Select the join type option (1, 2 or 3).
4. Click OK and run the query to compare the results.

.

6. Junction tables for many-to-many relationships

Many-to-many relationships must be resolved using a junction (link) table that contains foreign keys from both related tables.

Example: Students and Courses:

• Table Students (StudentID, Name).
• Table Courses (CourseID, Title).
• Table Enrolments (StudentID, CourseID, EnrolDate, etc.).

How to build a junction table

1. Create a new table called Enrolments.
2. Add StudentID and CourseID (matching data types from parent tables).
3. Set a composite primary key on StudentID + CourseID (or use a separate AutoNumber key if preferred).
4. Create relationships:
   • Students.StudentID → Enrolments.StudentID
   • Courses.CourseID → Enrolments.CourseID

7. Practical Activity

• Open the Relationships window.
• Create or review one-to-many relationships in your database.
• Enable Enforce Referential Integrity where appropriate.
• Experiment with inner and outer joins in a query and observe the results.
• Create a junction table to model a many-to-many relationship (e.g. Orders–Products).