Thread: relationships between tables
04-13-09, 11:25 #1Registered User
- Join Date
- Nov 2008
Unanswered: relationships between tables
I just got a copy of Access 2007 about a week ago and am working on a club membership database. I think I have a decent grip on my table layout at this point but one thing thats been bugging me is the various options presented when I drag-n-drop a relationship between a foreign key in one table and the corresponding primary key in another table (i.e. tblPhoneInfo.MemberID to tblMembers.MemberID). I've been checking the box for referential integrity, and *not* checking the boxes for cascade update and delete. I'm still waiting on a book I ordered specifically for Access 2007, so I'm a bit fuzzy yet on specifically what those three options do and how I might benefit and/or suffer from them. I *think* from what I've read in bits and pieces on various forums that the 'Cascade Update' is really only useful/necessary if using natural keys that might change, and that the 'Cascade Delete' could be a real can of worms in that deleting a member would result in the deletion of *all* records associated with them - which might be okay if I wanted to get rid of their email and phone numbers and such from the contact tables, but could wreak havoc in my other tables containing sales history and such. Is that much right so far?
Could somebody give me some theoretical examples of when it would, or would not be appropriate to use 'Maintain Referential Integrity, 'Cascade Update', and 'Cascade Delete'?
04-13-09, 17:05 #2Moderator
Provided Answers: 14
- Join Date
- Mar 2009
Your analysis is correct and this is not specific to Access 2007:
Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table.
Consider the situation where we have two tables: Employees and Managers. The Employees table has a foreign key attribute entitled ManagedBy which points to the record for that employee’s manager in the Managers table. Referential integrity enforces the following three rules:
We may not add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table.
If the primary key for a record in the Managers table changes, all corresponding records in the Employees table must be modified using a cascading update.
If a record in the Managers table is deleted, all corresponding records in the Employees table must be deleted using a cascading delete.
Have a nice day!