This Access tutorial explains how to create Relationships and the purpose of Enforce Referential Integrity.
You may also want to read:
Create Relationships in Excel PowerPivot
Access create Relationships
Create Relationships in Access is about telling Access how one table relates to another table. This process is not mandatory, you can still do everything without building any Relationships. However, if you are going to build an application, it can help you make sure all related tables are updated at the same time.
Assume that we have two tables – Compensation and Job. When an employee is hired, an Employee ID is created in Job, therefore it contains all Employee ID. Afterward a compensation record is created.
|Department||Employee ID||Employment Date|
In Access 20013, click on DATABASE TOOLS tab >Relationships
You will see there are three Tables inside. These are default Relationships, you can ignore them.
Drag the Job and Compensation tables from Navigation Pane to Relationships.
Highlight Employee ID in Job, hold down mouse button and drag over to Employee ID in Compensation.
It is important that you drag from Job to Compensation but not the other way round. The table you drag from (Job) is the master table storing the Employee ID, any changes you make to Employee ID is made in this table first. The table you drag to (Compensation) is the table being affected by the change of master table.
Now the below menu pops up.
At the bottom, the Relationship Type reflects whether the selected fields are Primary Key.
One to One – Both Employee ID are Primary Key (you must enable Enforce Referential Integrity to see the number 1 on the line)
One to Many – Only one of the Employee ID is Primary Key (you must enable Enforce Referential Integrity to see the number 1 and many symbol on the line)
Indeterminate (Many to Many) – Both Employee ID are not Primary Key
Above Relationship Type, there are three options relating to data Integrity.
You must add at least one of the Employee ID as Primary Key in order to enable Enforce Referential Integrity and its sub options. (Not allow Many to Many relationship)
Enforce Referential Integrity (add data)
This option prevents users to add a new Employee ID in Compensation where the new Employee ID does not exist in Job.
Cascade Update Related Fields (update data)
When checked, whenever you update Employee ID in Job, Employee ID in Compensation also changes.
Cascade Delete Related Fields (delete data)
When checked, whenever you delete Employee ID in Job, records containing that Employee ID in Compensation also get deleted.
Effect of Relationships on Subdatasheet
In the Design View of Job table, the property Subdatasheet Name is [Auto] by default.
After adding the Relationships, you can see the linked Compensation data in Job table Datasheet View.
If you don’t like it, you can change the Subdatasheet Name property to [None].