MS-Access / Getting Started

Defining Your First Relationship

A company can have several contacts, and any contact can belong to several companies or organizations. This means that companies have a many-to-many relationship with contacts. Defining a many-to-many relationship between two tables requires a linking table. Let's link the Companies and Contacts tables by defining the first half of the relationship-the one between Companies and the linking table, CompanyContacts. You can see that for the CompanyID primary key in the Companies table, there is a matching CompanyID foreign key in the CompanyContacts table. To create the relationship you need, click in the CompanyID field in the Companies table and drag it to the CompanyID field in the CompanyContacts table.

When you release the mouse button, Access opens the Edit Relationships dialog box.

You can also click the Edit Relationships command in the Tools group of the Design contextual tab on the ribbon to create a new relationship, but you have to fill in the table and field names yourself. The dragging operation does some of this work for you.

You'll notice that Access 2010 has filled in the field names for you. If you need to define a multiple-field relationship between two tables, use the additional blank lines to define those fields. (We'll do that in just a second.) Because you probably don't want any rows created in CompanyContacts for a nonexistent company, select the Enforce Referential Integrity check box. When you do this, Access 2010 ensures that you can't add a row in the CompanyContacts table containing an invalid CompanyID. Also, Access won't let you delete any records from the Companies table if they have contacts that are still defined.

Note that after you select the Enforce Referential Integrity check box, Access 2010 makes two additional check boxes available: Cascade Update Related Fields and Cascade Delete Related Records. If you select the Cascade Delete Related Records check box, Access 2010 deletes child rows (the related rows in the many table of a one-to-many relationship) when you delete a parent row (the related row in the one table of a one-to-many relationship). For example, if you removed a company from the table, Access 2010 would remove the related company contact rows. In this database design, the CompanyID field has the AutoNumber data type, so it cannot be changed once it is set. However, if you build a table with a primary key that is Text or Number (perhaps a ProductID field that could change at some point in the future), it might be a good idea to select the Cascade Update Related Fields check box. This option requests that Access automatically update any foreign key values in the child table (the many table in a one-to-many relationship) if you change a primary key value in a parent table (the one table in a one-to-many relationship).

You might have noticed that the Show Table dialog box, gives you the option to include queries as well as tables. Sometimes you might want to define relationships between tables and queries or between queries so that Access 2010 knows how to join them properly. You can also define what's known as an outer join by clicking the Join Type button in the Edit Relationships dialog box and selecting an option in the Join Properties dialog box. With an outer join, you can find out, for example, which companies have no contacts or which products haven't been sold.

We recommend that you do not define an outer join relationship between two tables. Access 2010 automatically links two tables you include in a query design using the relationships that you have defined. In the vast majority of cases, you will want to include only the matching rows from both tables. If you define the relationship as an outer join, you will have to change the link between the two tables every time you include them in a query.

We also do not recommend that you define relationships between queries or between a table and a query. If you have done a good job of naming your fields in your tables, the query designer will recognize the natural links and define the joins for you automatically. Defining extra relationships adds unnecessary overhead in your database application.

Click the Create button to finish your relationship definition. Access draws a line between the two tables to indicate the relationship. Notice that when you ask Access to enforce referential integrity, Access displays a 1 at the end of the relationship line, next to the one table, and an infinity symbol next to the many table. If you want to delete the relationship, click the line and press the Delete key.

You now know enough to define the additional one-to-many simple relationships that you need. Go ahead and define a relationship on ContactID between the Contacts and CompanyContacts tables to complete the other side of the many-to-many relationship between companies and contacts, a relationship on ContactID between the Contacts and ContactEvents tables, and a relationship on ProductID between the Products and ContactProducts tables. For each relationship, be sure to select the Enforce Referential Integrity check box.

[Previous] [Contents] [Next]

In this tutorial:

  1. Designing Client Tables
  2. Creating a New Database
  3. Creating a New Empty Database
  4. Creating Your First Simple Table by Entering Data
  5. Creating a Table Using Application Parts
  6. Creating a Table Using Data Type Parts
  7. Creating a Table in Design View
  8. Understanding Field Data Types
  9. Setting Field Properties
  10. Nulls and Zero-Length Strings
  11. Defining Simple Field Validation Rules
  12. Defining Input Masks
  13. Defining a Primary Key
  14. Defining a Table Validation Rule
  15. Understanding Other Table Properties
  16. Defining Relationships
  17. Defining Your First Relationship
  18. Creating a Relationship on Multiple Fields
  19. Adding Indexes
  20. Multiple-Field Indexes
  21. Setting Table Design Options
  22. Creating a Default Template for New Databases
  23. Printing a Table Definition
  24. Database Limitations