Designing Relationships and Referential Integrity

Connect tables correctly and prevent orphaned records with relational design.

What you will learn

• Recognise one-to-many relationships
• Create matching primary and foreign keys
• Understand referential integrity and cascade choices

Before you begin

Use a practice database containing non-sensitive sample information. Access versions can place commands differently, so use the ribbon search box when a named command is not immediately visible. Back up the file before structural changes.

Connect related subjects

A customer can have many orders, while each order belongs to one customer. CustomerID is the primary key in Customers and appears as a foreign key in Orders.

The related fields must use compatible data types and field sizes. An AutoNumber primary key is normally matched by a Number field with Long Integer size.

Create relationships

Open Database Tools and Relationships, add the relevant tables, then drag the primary key to the matching foreign key. Review the relationship before selecting Create.

Defined relationships help Access propose joins when creating queries, forms and reports. They also document the database structure.

Protect consistency

Enforce Referential Integrity prevents an order from referring to a customer that does not exist. Cascade Update and Cascade Delete are separate choices with different risks.

Cascade Delete can remove many related records at once. Enable it only when that behaviour is deliberately required and tested with backups.

Guided practice

1. Create an Orders table with OrderID, CustomerID, OrderDate and TotalAmount.
2. Relate Customers.CustomerID to Orders.CustomerID.
3. Enable referential integrity and inspect the relationship symbol.

Knowledge check

1. What is a foreign key?
2. Why must related field types match?
3. Why should Cascade Delete be chosen cautiously?

Completion standard

Continue when you can complete the guided practice safely and explain the knowledge-check answers in your own words.