Table Relationships in Microsoft Access
Relationships between Tables are formed by creating a common Key between the Tables. The Key in the parent Table is called the Primary Key. The Key in the child Table is called the Foreign Key.
Related tables with one-to-one or one-to-many relationships should have referential integrity implemented. Referential integrity ensures the relationships between Tables in a database are accurate.
An example of relationships between Tables
When a relationship is established between two Tables, an index is created using the Primary Key. This makes accessing the two Tables much faster. Using an index also eliminates redundant data.
The Primary Key ➀ is called "ID" in the Customers Table. The Foreign Key ➁ is called "Customer ID" in the Orders Table.
The Primary Key must be unique and should be numeric.
Referential integrity is at the very core of relational database design. It is essential that Referential integrity be enforced, even at the expense of performance concerns.
A Referential integrity constraint keeps the data consistent, minimises logic bugs and ensures that the Microsoft Access optimiser creates efficient queries.
Referential Relationships will enforce cascading deletes (or updates) to ensure orphan records are not created. With a delete, the removal of the parent record will automatically delete all child records in the related Table.
Enforcing Referential Integrity
When adding a Link between two Tables, the Relationship has these options:
- Cascade Update Related Fields: Microsoft Access automatically updates the primary key to the new value in all related records
- Cascade Delete Related Fields: Microsoft Access automatically deletes the records in related tables
Microsoft Access takes care of all this without the need to write any code.
Visual Basic Development
Referential relationships improve the code quality. Strictly enforced, Referential relationships produce more errors in testing but fewer errors in production.
Foreign Keys help in the development of an Access database system.
- The Query designer documents the system with an overview of all related Tables
- A link is automatically drawn when the two tables are placed on the Query designer
- Using the Query designer, data can be extracted from multiple tables without complex code. Microsoft Access figures out the SQL code automatically.
Referential Integrity checking creates an additional load on the system. When data is inserted, Microsoft Access must check for the row's existence in the parent Table.
The performance overhead of maintaining Foreign Key relationships is minimal. This could possibly be an issue where there are transactions with hundreds of inserts/deletes per second. In this case, the Integrity constraints could be temporarily removed for the run.
There are literally hundreds of performance issues that can impact performance. These should be minimised before thinking of removing the Integrity constraints. For example, returning all the fields when reading a Table row, rather than just the fields required, would have a far greater impact than Referential Integrity. Or not having Unicode Compression on text fields.
When converting to SQL Server, you will find that SQL Server is much stricter that Microsoft Access. SQL Server insists that every table have a numeric primary Key.
A SQL Server database without related Tables will be extremely inefficient. As would Microsoft Access.