This post follows from the post: Creating & using a “lookup table” in Access. In that exercise, we created a table with the details of some Learners, tblLearners, and a table with a list of unique suburb names, tblSuburbs. We then used tblSuburbs as the Row Source for the Suburbs field Combo Box in tblLearners.
This approach introduces an interesting problem: what happens if you delete a record from the Suburbs table where the specific Suburb deleted was actually being used as the suburb value for one of the records in tblLearners? Doing this would result in orphan records in the tblLearners table which would, in turn, mean your data is incomplete.
To prevent this from happening, modern relational databases implement referential integrity. To enforce referential integrity we must create a relationship between the two tables:
- Select the Database Tools menu tab
- Click on the Relationships button
- Click on the Show Table button
- Add the tblLearners and tblSuburbs tables
Left-click-drag the suburbID field in tblSuburbs and drop it onto the Suburb field in tblLearners. The Edit Relationships dialog window will pop up.
Check the “Enforce Referential Integrity” checkbox and click the “Create” button. The screenshot below now shows the relationship between the two tables. The 1 (one) and the infinity symbol (many) are to indicate that this is a one-to-many relationship between the tblSuburbs and tblLearners tables. This relationship describes the real-world scenario: a suburb only appears one time in the tblSuburbs table while it can appear many times in the tblLearners table — a suburb only exists once in reality and many learners can live in the same suburb.
Ensure that the suburbs have been selected for the learners in tblLearners and then attempt to delete a record for a suburb that is in use from tblSuburbs, the lookup table:
Open the lookup table, tblSuburbs and notice that the relationship is further demonstrated with the expand/collapse button to the left of each record which displays related records from tblLearners.