How to Create Database Relationships with Referential Integrity in Microsoft Access on Windows 11
Posted on 18th June 2023
What is Referential Integrity?
Referential Integrity is a database concept that maintains the consistency and validity of data in a database. It does this by ensuring that relationships between data in different tables are maintained, and that data is not orphaned. In other words, Referential Integrity ensures that data is consistent across a database, and that data is not corrupted or lost.
How to Create Database Relationships with Referential Integrity in Microsoft Access
Microsoft Access provides a graphical interface for creating and editing database relationships. To create a new relationship, open the Relationships window by clicking the Relationships button on the Database Tools tab.
In the Relationships window, click the Add button. This will open the Add Table dialog box. Select the table that you want to add to the relationship, and then click the Add button.
Once you have added the tables that you want to include in the relationship, you can create the relationship by dragging the field that you want to relate from one table to the field that you want to relate it to in the other table. For example, to create a relationship between the Customers table and the Orders table, you would drag the CustomerID field from the Customers table to the CustomerID field in the Orders table.
Once you have created the relationship, you can edit the relationship properties by right-clicking on the relationship line and choosing Edit Relationship from the context menu. In the Edit Relationship dialog box, you can choose to enforce referential integrity, and you can also choose what action should be taken if a record in the primary table is deleted or updated.
Enforcing Referential Integrity
When you enforce referential integrity, Access will prevent you from adding records to the related table that do not have a matching record in the primary table. For example, if you have a Customers table and an Orders table, and you enforce referential integrity between the two tables, you will not be able to add an order for a customer who does not exist in the Customers table.
To enforce referential integrity, open the Edit Relationship dialog box for the relationship that you want to edit. In the Edit Relationship dialog box, select the Enforce Referential Integrity check box, and then click the OK button.
Cascade Actions
When you delete or update a record in the primary table, you can choose to have the related records in the related table deleted or updated as well. This is known as a cascade action, and it can be configured in the Edit Relationship dialog box.
To configure a cascade action, open the Edit Relationship dialog box for the relationship that you want to edit. In the Edit Relationship dialog box, choose the action that you want to be taken from the drop-down list next to the Delete and Update options.
The options for the Delete and Update actions are as follows:
NO ACTION: No action is taken on the related records in the related table.CASCADE: The related records in the related table are deleted or updated.SET NULL: The foreign key values in the related table are set to NULL.SET DEFAULT: The foreign key values in the related table are set to the default value.
Conclusion
Referential integrity is a database concept that maintains the consistency and validity of data in a database. It does this by ensuring that relationships between data in different tables are maintained, and that data is not orphaned. In other words, referential integrity ensures that data is consistent across a database, and that data is not corrupted or lost.
Microsoft Access provides a graphical interface for creating and editing database relationships. To create a new relationship, open the Relationships window by clicking the Relationships button on the Database Tools tab. In the Relationships window, click the Add button. This will open the Add Table dialog box. Select the table that you want to add to the relationship, and then click the Add button.
Once you have added the tables that you want to include in the relationship, you can create the relationship by dragging the field that you want to relate from one table to the field that you want to relate it to in the other table. For example, to create a relationship between the Customers table and the Orders table, you would drag the CustomerID field from the Customers table to the CustomerID field in the Orders table.
Once you have created the relationship, you can edit the relationship properties by right-clicking on the relationship line and choosing Edit Relationship from the context menu. In the Edit Relationship dialog box, you can choose to enforce referential integrity, and you can also choose what action should be taken if a record in the primary table is deleted or updated.
When you enforce referential integrity, Access will prevent you from adding records to the related table that do not have a matching record in the primary table. For example, if you have a Customers table and an Orders table, and you enforce referential integrity between the two tables, you will not be able to add an order for a customer who does not exist in the Customers table.
To enforce referential integrity, open the Edit Relationship dialog box for the relationship that you want to edit. In the Edit Relationship dialog box, select the Enforce Referential Integrity check box, and then click the OK button.
When you delete or update a record in the primary table, you can choose to have the related records in the related table deleted or updated as well. This is known as a cascade action, and it can be configured in the Edit Relationship dialog box.
To configure a cascade action, open the Edit Relationship dialog box for the relationship that you want to edit. In the Edit Relationship dialog box, choose the action that you want to be taken from the drop-down list next to the Delete and Update options.
The options for the Delete and Update actions are as follows:
NO ACTION: No action is taken on the related records in the related table.CASCADE: The related records in the related table are deleted or updated.SET NULL: The foreign key values in the related table are set to NULL.SET DEFAULT: The foreign key values in the related table are set to the default value.
Referential integrity is a database concept that maintains the consistency and validity of data in a database. It does this by ensuring that relationships between data in different tables are maintained, and that data is not orphaned. In other words, referential integrity ensures that data is consistent across a database, and that data is not corrupted or lost.
