Referential integrity are rules built into Microsoft Access to help prevent Insertion, Update, and Deletion Anomalies , and defines the relationships between two tables.
If Cascade update and delete are enabled, referential integrity guarantees that updates and deletes between two related tables remain coordinated. If an Order number is updated, the related Order Details are also updated. If an Order is deleted, then the related Order Details are also deleted. Cascade delete can be very helpful to prevent orphan data, but is not always required.
The examples below are from Microsoft's Northwind Traders Sample Database 2007.
Below are simple linked tables with no referential integrity enabled. The link is created and referential integrity is automatically enabled if you use the Lookup wizard within Microsoft Access to add a Foreign Key to your table.
The most common relationship is One-to Many where one record in the first table has a Foreign Key from a second table that could have many entries. In the example below one Order could have many Order Details. Order Details would be the line items that hold the details for the Order.
A Many-to-Many table contains the Foreign Keys from two or more tables with a One-to-Many relationship, and can also be called a Junction table. This allows you to combine data from multiple tables as shown in the Order Details table below. Each record contains data from the joined tables, so you can combine orders and products for line items on the Order. (Also notice that there are also Purchase Order ID, and Inventory ID which would also be foreign keys).
A One-to-One relationship is rarely used since related data fields should be in one table, but there are exceptions:
- Divide a table that has too many fields.
- To enable security for parts of the data.
- Temporary related data.
If you right click on the relationship line you can edit the relationship.
The image below shows how easy it is to enable Referential Integrity; however there are rules for existing data that must be applied to avoid an error. (Please see Microsoft's link at the bottom of the page for details).
If the tables have no data Referential Integrity will be enabled with the check.
Below enables Referential Integrity as well as Cascade Delete, so that when an order is deleted, the associated Order Details will also be deleted.
For more details please read Microsoft's Access Referential Integrity Information.