Create a one-to-many relationship in Access
The one-to-many relationship is the most common relationship in database design. This tutorial explains how to create a one-to-many relationship in Microsoft Access 2010. In order to create the relationship you first need two tables. I assume you know how to create tables, if not, have a look at the article how to create a table in Access 2010.
Creating the one-to-many relationship
I created two tables for this tutorial, a Customer table which stores customer an and Order table which stores orders. The Customer table already contains some data.
The Order table is empty and contains a customer_id field. In order to create the one-to-many relationship we are going to link the customer_id field in the Order table to the customer_id field in the Customer table, so that every Order can be linked to a Customer. The customer_id field in the Order table is called a foreign key.
The Customer table
Empty order table. The customer_id field is not linked to the Customer table yet.
Take the following steps to create the one-to-many relationship.
Follow these steps to create the one-to-many relationship
- Create two tables. The many-table (Order in this example) must have a field that will become the foreign key. This field must have the same data type as the primary key it will refer to (the primary key of Customer in this example). You can choose any name for the field. The name of a foreign key field doesn't have to be the same as the primary key field it refers to, but it is allowed.
- Select the Database Tools tab on the ribbon
and then click the Relationships button. This will open the Access
- Access will ask you which tables you want to show on the
relationship screen. Select the two tables you want to create the
one-to-many relationship for and click Add. The
tables will then appear on the relationship screen.
Note: you can always add tables later by dragging them from the list on the left onto the relationships screen.
- The easiest way to create the one-to-many relationship is to
select the customer_id in the Customer table with
your mouse and while keeping the mouse button down, dragging the
field over to the customer_id field in the Order tablea and
Drag and drop the primary key of Customer to the (soon to be) foreign key in the Order table.
- When you release the mouse over the customer_id field in the
Order table Access will show you the Edit Relationships
As you can see, Access already filled out the most important information for you, based on you selection of the customer_id fields. The customer_id field of the Customer table is on the left ("Table") and the customer_id field of the Order table is on the right ("Related table"). This is what we want, because we want the customer_id field in the Order table to relate to the customer_id field of the Customer table. If you click Create right now you will have created a one-to-many relationship. Below you will find an explanation of the Enforce Referential Integrity, Cascade Update Related Fields and Cascade Delete Related Records options.
The Enforce Referential Integrity option
If you select the Enforce Referential Integrity option Access will make sure that each record in the Order table refers to an existing record in the Customer table. Selecting this option makes it impossible to create Order records thar refer to an non-existent customer. You should select Enforce Referential Integrity by default, because it protects the integrity of your data.
The Cascade Update Related Fields option
If you select the Cascade Update Related Fields option Access will change the foreign key if the primary key it refers to changes. In case of our one-to-many example this means that if the primary key of a customer (one) changes, Access will automatically update the foreign keys that refer to this customer in the Order table (many). The Cascade Update Related Fields option also protects the integrity of your data as it prevents records from becoming detached from their related records.
The Cascade Delete Related Records option
The Cascade Delete Related Records option ensures that whenever a Customer (one) record is deleted, than the related records (many) in the Order table are also deleted. You should select this option if the many-part of the relationship has no use or is not needed anymore without the one-part. In case of our one-to-many example this would mean that when a customer is deleted als his/her orders are deleted from the order table as well.
The one-to-many relationship on the Relationships screen. The 1 indicates the 1 side of the relationship and the infinity symbol (∞) the many side.
The final step in creating the one-to-many relationship is deciding which of the integrity options you select. Finally, click Create to create the relationship. The result will look like the image above.