MS-Access / Getting Started

---

Relationship Types

Three types of relationships can be set in a relational database:

One-to-one relationship:
For each record in one table, there is one and only one matching record in a different table. It is as if two tables have the exact same primary key. Typically, data from different tables in a one-to-one relationship will be combined into one table.

One-to-many relationship:
For each record in one table, there may be zero, one or many records matching in a separate table. For example, you might have an invoice header table related to an invoice detail table. The invoice header table has a primary key, Invoice Number. The invoice detail table will use the Invoice Number for every record representing a detail of that particular invoice. This is certainly the most common type of relationship you will encounter.

Many-to-many relationship:
Used decidedly less often, this relationship cannot be defined in Access without the use of a mapping table. This relationship states that records in both tables can have any number of matching records in the other table.

In the sample database that came with this book, relationships have already been established between the tables. Take a look at some of these relationships to get a better idea of how they can be set and changed. Go to the application ribbon and select the Database Tools tab. Then select Relationships to view the existing relationships for this database. As shown in Figure below, the tables are represented with lines between them signifying the relationships.

A one-to-many relationship between tables can be identified by the infinity symbol on the line connecting the tables.

TIP: You may have noticed that some of the objects you see in the figures here seem to have broken free from the tabbed layout. In Access 2007, the default display layout for the various objects in a database is the tabbed documents view. You can easily switch to the classic overlapping windows view by taking the following steps:

1. Click the Office icon on the upper left-hand corner.
2. Click Access Options. This activates the Access Options dialog box.
3. Click the Current Database button.
4. Select Overlapping Windows in the Application Options section, under Document Window Options.
5. Click OK.

In the Relationships window, you can add tables by right-clicking the display and selecting Show Table. After your tables have been added, relationships can be established by dragging one field from one table to a field in another table. This opens the Edit Relationships dialog box.

Based on the use of primary keys and foreign keys, the Edit Relationships dialog box attempts to guess the kind of relationship that you want to establish.

You can also edit an existing relationship by right-clicking the line connecting the two tables and selecting Edit Relationships.

Referential Integrity

In addition to establishing relationships between tables, you are able to enforce certain rules that guide these relationships. For example, if you have an Invoice table with a Customer_Number foreign key, you will not be able to add an invoice for a customer number that does not exist in the other table. You must add the new customer to the customer table before the new foreign key can be placed in the invoice table. Also, if you attempt to delete a customer from a table when there are matching invoices for that customer, an error will occur. Referential integrity enables you to use Access to maintain the relationships that you have created.

By selecting the Enforce Referential Integrity check box in the Edit Relationships dialog box, you tell Access to first verify that a valid relationship exists between the two tables. Other conditions that need to be met to establish referential integrity are:

• The field that is used to match the two tables must be a primary key in one of those tables.
• The field that is used to match the two tables must be of the same data type.
• After the validity of the relationship has been established, referential integrity will be continuously enforced until switched off.

Cascading Updates and Deletes

The main purpose of referential integrity is two-fold: first, to prevent changing a primary key value for which there are matching foreign key values in a second table, and second, to prevent deleting a primary key value for which there are matching foreign key values. These two rules of referential integrity can be overridden by clicking either Cascade Update Related Fields or Cascade Delete Related Records.