Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 36 ยท Tables and Relationships

Relationships and Referential Integrity in Access

Link Access tables with one-to-many, one-to-one and many-to-many relationships, set referential integrity, cascade updates and deletes, and use junction tables.

A relational database earns its name from relationships: rules that connect the rows of one table to the rows of another. Instead of typing a customer's address on every order, an order stores a customer number, and Access follows that number back to the customer. Relationships also let Access enforce consistency, so you cannot create an order for a customer who does not exist.

This lesson explains the three relationship types, how to build them in the Relationships window, what referential integrity and cascade options do and how to model many-to-many links with a junction table.

Primary and foreign keys

Every relationship joins a primary key in one table to a foreign key in another. The table with the primary key is the parent (or primary) table; the table that holds the foreign key is the child (or related) table.

In a Customers and Orders pair, Customers.CustomerID is the primary key and Orders.CustomerID is the foreign key. The two fields must have the same data type. An AutoNumber primary key matches a Number (Long Integer) foreign key.

Three relationship types

One-to-many One customer, many orders
One-to-one One person, one profile
Many-to-many Students and courses, via a third table

One-to-many is by far the most common.

1 / 2

One-to-many

This is the everyday relationship. One row in the parent table can match many rows in the child table, but each child row matches exactly one parent. One customer places many orders; each order belongs to one customer. A department has many employees; each employee belongs to one department.

You recognise it by the position of the foreign key: it always lives on the many side.

One-to-one

Each row in table A matches at most one row in table B. This is rare. It appears when you split a table to separate rarely used or sensitive fields (an employee's salary details), or when two kinds of entity share a key. To model it, make the foreign key in the second table also its primary key, or put a unique index on it. When you draw the relationship, Access shows 1 at both ends.

Many-to-many

Many students attend many courses. A single foreign key cannot model this, because a student field holding several course numbers would break the one-fact-per-field rule. The solution is a third table, the junction (linking or bridge) table. It holds one foreign key to each side, and each row records one pairing. A many-to-many relationship is then two one-to-many relationships.

Text
Students 1 โ”€โ”€< Enrolments >โ”€โ”€ 1 Courses

Junction tables often carry extra facts about the pairing, like the enrolment date or a grade. You saw one in the keys lesson, with a composite primary key made of the two foreign keys.

Building relationships in Access

  1. Close all open tables.
  2. Choose Database Tools > Relationships.
  3. If the window is empty, click Add Tables (or Show Table), select your tables and close the dialog.
  4. Drag the primary key field from the parent table onto the matching foreign key in the child table.
  5. In the Edit Relationships dialog, tick Enforce Referential Integrity.
  6. Optionally tick Cascade Update Related Fields and Cascade Delete Related Records.
  7. Click Create.

A line connects the tables. If referential integrity is on, the line shows 1 at the parent end and the infinity sign (โˆž) at the child end. Save the layout with Ctrl+S. Double-click a line to edit it, or select it and press Delete to remove it.

The Join Type button in the dialog sets how queries will join these tables by default. The default is an inner join, which returns only matching rows. You will use the other join types in the joins lesson.

The relationships you draw in this window define the default joins that new queries inherit, and they enforce data rules. Draw them early, before entering data, so Access can catch errors as you type.

Referential integrity

Enforce Referential Integrity adds these rules:

  1. You cannot add a child row whose foreign key does not exist in the parent table (no orphans). A new order must refer to a real customer.
  2. You cannot delete a parent row if child rows still refer to it.
  3. You cannot change a parent's primary key value if child rows refer to it.

Access can only turn it on if the existing data already obeys the rules and both fields have compatible types. If it refuses, find the orphaned rows with an unmatched query and fix or delete them. The queries chapter shows how.

SQL equivalent: a FOREIGN KEY ... REFERENCES constraint. Try breaking the rule here:

SQL Referential integrity in action

Press Run (or Ctrl + Enter) to see the result here.

The error FOREIGN KEY constraint failed matches Access's message "You cannot add or change a record because a related record is required in table 'Customers'."

Cascade options

Cascades let Access propagate changes automatically so you do not have to do the extra work.

Cascade Update Related Fields. If a parent's primary key changes, Access updates the foreign keys in all child rows. This is rarely needed with AutoNumber keys, since they never change. It matters with natural keys such as a product code.

Cascade Delete Related Records. If you delete a parent, Access also deletes all its children. Delete a customer and all their orders disappear. This is powerful and dangerous. Access warns you with a dialog stating how many records will go, but a quick click-through can erase important data.

Prefer to leave it off for business records such as orders and invoices, where history matters. Turn it on for truly dependent detail rows. Deleting an order should usually delete that order's line items, for example.

SQL Cascade delete

Press Run (or Ctrl + Enter) to see the result here.

In Access SQL you create the same behavior with a constraint such as ALTER TABLE OrderLines ADD CONSTRAINT fkOrder FOREIGN KEY (OrderID) REFERENCES Orders (OrderID) ON DELETE CASCADE;, or you tick the Cascade checkbox in the dialog.

Many-to-many in practice

Here is a complete many-to-many model with a junction table and the query that reads it:

SQL Students, courses and a junction table

Press Run (or Ctrl + Enter) to see the result here.

Common relationship problems

  • "Cannot create relationship" or "no unique index found": the parent field is not a primary key or unique index. Make it one.
  • Data type mismatch: AutoNumber parents need Long Integer children, not Short Text.
  • Can't enforce integrity because of orphans: find and fix existing rows that point nowhere.
  • Subdatasheet plus signs everywhere: Access shows related records in a datasheet by default. Turn that off with the table's Subdatasheet Name property set to [None].
  • Circular relationships and ambiguity: many links between the same pair of tables give queries more than one path. Keep the diagram simple.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a Customers and Orders design, where does the foreign key live?

    The foreign key is stored in the child (many) table and points to the parent's primary key.
  2. 2. How do you model a many-to-many relationship?

    A junction table turns a many-to-many link into two one-to-many relationships.
  3. 3. What does Enforce Referential Integrity prevent?

    It blocks orphan rows and stops you deleting or changing parent keys that still have children.
  4. 4. When is Cascade Delete Related Records most dangerous?

    Deleting a customer would also delete all their orders, which usually destroys history you want to keep.

Summary

  • Relationships link a parent table's primary key to a child table's foreign key; the foreign key sits on the many side.
  • One-to-many is the norm; one-to-one is rare; many-to-many needs a junction table.
  • Build them in Database Tools > Relationships by dragging the key field and ticking Enforce Referential Integrity.
  • Referential integrity stops orphan rows and protects parents from deletion; cascade options automate updates and deletes, so use them carefully.
  • Draw relationships before entering data so Access can catch errors early.

Next: Select Queries and Query Design View