Using the Relationships Window

It's fine to use the Lookup Wizard or the Table Analyzer, but you can create relationships yourself. First, open the Relationships window shown in Figure 6.11 by clicking the Relationships button on the Database toolbar. If that button is not visible, press F11 to give focus to the Database window. Then, the button should be available.

Figure 6.11. Open the Relationships window.

The window contains two field lists, one for Plants and one for Types. (If your window is empty, click the Show All Relationships button on the Relationship toolbar.) In addition, there's a line between the two tables, which is known as a join line . When you created the lookup field for the TypeID field in the Plants table, the wizard created this relationship.

When you need to create a relationship, drag a field from one list to another. In almost all cases, you'll drag the primary key field to its counterpart in the related table. If you look closely at the existing join line, you'll see that it connects the two TypeID fields in both tables.

You can easily find the primary key fields in both tables because Access displays the primary key fields in bold text. Most of the time, you'll rely on the Relationships window to create permanent relationships between tables.

You still have a relationship you need to createthe one between the Catalogs and Plants tables. So, let's get started:

1. Click the Show Table button on the Relationship toolbar.

2. In the resulting Show Table dialog box, select Catalogs , and then click Add . Alternatively, you could double-click Catalogs.

3. Close the Show Table dialog box by clicking Close.

4. Select the Name field in the Catalogs list, but don't release the mouse. Access will display the field pointer shown in Figure 6.12.

   Figure 6.12. Access displays the field pointer when you drag a field from one list to another.

   

5. Still holding down the mouse, drag the Name field to the CatalogName field in the Plants list and then release the mouse. Access displays the Edit Relationships dialog box.

6. Check the Enforce Referential Integrity option, as shown in Figure 6.13; then click the Create button. (We'll introduce referential integrity in the next section.)

   Figure 6.13. Specify relationship properties in the Edit Relationships dialog box.

   

Figure 6.14 shows the new relationship between the two tables. Notice that the join line is very different from the one connecting the Plants and Types tables:

Figure 6.14. The join line reflects the relationship you just created.

• The 1 next to the Catalogs list defines the primary key table, or the one side of the relationship Only one matching record exists in this table, which makes sense because the relationship is based on the primary key value, which as you've already learned, must be a unique value.

• The infinity symbol next to the Plants list indicates the many sides of this relationship Many related records might exist in the Plants table that match any given record in the Catalogs table.

The Many Sides to Relationships

We just introduced you to a new concepta one-to-many relationship between the Plants and Catalog tables. There are three types of relationships. A thorough discussion of the relationship types is beyond the scope of this book, but you should be familiar with the terms:

• One-to-one Has only one matching record in both tables. You won't see these relationships very often.

• One-to-many The most common relationship. Each record in the primary key table can have many records in the related table. For instance, each catalog can match many records in the actual Plants table, but each plant matches only one catalog.

• Many-to-many Both tables can have many records in this relationship. For instance, you could add a table of colors to describe the bloom on each plant. Each color could refer to many plants, and each plant could consist of more than one color .

Don't spend too much time thinking about the types of relationships right now. Fortunately, Access does a good job of interpreting the relationships between tables.

Modifying a Relationship

Now that you know how to create a relationship, let's modify one. Specifically, let's turn on referential integrity for the relationship between the Plants and Types tables. Referential integrity refers to a set of rules that protects data from changes that don't make sense. For example, think about the relationship between plants and types. What would happen if you deleted the first row from the Types table? You'd no longer be able to look up the descriptions of plants with a TypeID of 1. That's the sort of problem referential integrity prevents . We'll demonstrate how this works after adding referential integrity to the relationship.

To display the Edit Relationships dialog box for the relationship between the Plants and Types tables, double-click the join line between those two tables. In the resulting dialog box, check the Enforce Referential Integrity option. Then click OK , which is a little different from the last time when you clicked Create when you were done (refer to Figure 6.13).

After you modify the relationship between the Plants and Types tables, Access updates the join line accordingly , as shown in Figure 6.15. It's easy to see that the relationship between the two tables enforces referential integrity in a one-to-many relationship.

Figure 6.15. Access updates the join line between the Plants and Types tables.

You probably noticed that several other buttons are available in the Edit Relationship dialog box (refer to Figure 6.13). Let's take a brief look at the remaining edit possibilities:

• Table/Query Always lists the primary key side of a relationship. Specifies the appropriate key field(s) in the cells just below this control. Access usually defaults to the correct fields.

• Related Table/Query Always lists the foreign key side of a relationship. Specifies the appropriate key field(s) in the cells just below this control. Access usually defaults to the correct fields.

• Join type Displays another dialog box that lets you modify the type of join. This is an advanced operation that you won't need in this book.

• Create New Offers more field possibilities for multiple field keys. (See Chapter 4 to learn more about primary and foreign keys.)

• Cascade Update Related Fields A referential integrity feature that's available only when the Enforce Referential Integrity option is selected. This option automatically updates any related foreign key values when you change the value of a primary key. We recommend you not use this option unless you have a specific reason to do so.

• Cascade Delete Related Fields A referential integrity feature that's available only when the Enforce Referential Integrity option is selected. This option automatically deletes any related foreign key records when you delete a primary key record. We recommend you not use this option unless you have a specific reason to do so.

• Relationship Type Lists the type of relationship between the tables.

Close the Relationships window. When Access prompts you to save changes, click Yes.

Using Referential Integrity

Previously, we told you to select the Enforce Referential Integrity option in the Edit Relationships dialog box (refer to Figure 6.13). Referential integrity is simply a set of rules that protects your data because Access restricts the records you can add and delete. With referential integrity turned on

• You can't change a primary key value if a related record exists in another table.

• You can't enter a foreign key value if that value doesn't already exist as a primary key in the related table.

These rules will make more sense if you see them in action, so let's return to your tables and make a few changes. First, open the Catalog table and try to delete the first record. When you do, Access displays the warning message shown in Figure 6.16. Click OK to clear the message.

Figure 6.16. Referential integrity won't allow you to delete the record for Gurney's catalog.

Note

With the Enforce Referential Integrity option turned off, Access enables you to update any value or add and delete any records as long as you don't violate any data type or validation rules (which you'll learn about in Chapter 11, "Customizing Your Tables"). You must turn on referential integrity to use it. We recommend you not use either of the cascading options unless you have a specific reason to do so, and then we suggest you never turn them on permanently.

You can't delete the record for Gurney's catalog because two plants, cosmos and German Chamomile, are related to that catalog. As long as even one of those records is there, Access won't let you delete the record for the Gurney's catalog.

Deleting that record would create what's known as an orphan related records where the foreign key value (in this case, Gurney's) doesn't match a record in the related table. In other words, if you deleted the record for the Gurney's catalog, there would be no way to know from which catalog you purchased chamomile and cosmos seeds . This doesn't seem too terribly important, but can you imagine not knowing which customer placed a particular order? That would be a much more serious problem.

Now, let's see what happens when you try to modify a primary key value. In the Catalogs table, try to change Gurney's to Gurneys (delete the apostrophe character). You'll have to move the insertion point to another row to complete the action. When you do, Access displays another warning message: The record cannot be deleted or changed because table 'Plants' includes related records . Click OK to clear the message and then press Esc to clear the change. The problem is the same as before. Changing Gurney's to any other value would strand orphanscosmos and German Chamomilein the Plants table.