Relationship in access, Creating the one-to-many relationship

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.

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

1. 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.
2. Select the Database Tools tab on the ribbon and then click the Relationships button. This will open the Access relationships screen.
   
   
3. 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.
4. 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 releasing it.
   
   
   

   Drag and drop the primary key of Customer to the (soon to be) foreign key in the Order table.
5. When you release the mouse over the customer_id field in the Order table Access will show you the Edit Relationships window.
   
   
   
   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.