How solid is your understanding of Microsoft Access table relationships? This quick quiz covers explicit relationships, subdatasheets, orphaned records, referential integrity, and what really happens when you try to delete a customer who still has contacts attached.
Give yourself a few seconds to answer each question before checking the explanation. If your records are wandering around without parents, this is a good place to find out why.
Question 1: What is an explicit relationship in an Access database?
A. A relationship saved at the database level between tables
B. A calculated field that combines two tables
C. A relationship created only while a query is open
D. A pinky promise between your records
Answer: A. An explicit relationship is one that is saved at the database level, usually in the Relationships window. Access queries can create joins as needed, but a saved relationship is a global relationship that makes other relationship features available.
Question 2: After creating a one-to-many relationship, where can Access display a subdatasheet of related child records?
A. Only in a report's Page Footer
B. On the parent table, the one side of the relationship
C. On the child table, the many side only
D. Inside every AutoNumber field
Answer: B. Access can display the related child records as a subdatasheet on the parent table, which is the one side of the relationship. For example, a customer record can expand to show that customer's contacts. This is where those little plus buttons come from.
Question 3: What is an orphaned record in a one-to-many relationship?
A. A parent record that has no children yet
B. A record with an AutoNumber primary key
C. A child record whose related parent no longer exists
D. A record that has wandered away from the Navigation Pane
Answer: C. An orphaned record is a child record that still contains a foreign key value, but its parent record is gone. For example, if a contact still has a CustomerID value after that customer has been deleted, the contact is an orphan. Not good. Databases are not orphanages.
Question 4: With referential integrity enforced, what happens if you try to delete a customer who still has related contact records?
A. Access blocks the deletion until the related contacts are handled
B. Access silently changes each contact's CustomerID to zero
C. Access automatically copies the contacts into CustomerT
D. Access deletes the entire database for dramatic effect
Answer: A. Access blocks the deletion. Referential integrity prevents you from deleting the parent customer while related child contacts still exist. You must delete the contacts, reassign them to another customer, or use cascade delete if that is truly what you intend. Be careful with cascade deletes. They are useful, but they can remove a lot of records very quickly.
Question 5: Which setup is required before Access can enforce referential integrity between CustomerT and ContactT?
A. CustomerID must be Short Text in both tables
B. The matching fields must have compatible data types, and the parent key must be unique
C. ContactT must contain at least one contact for every customer
D. Both tables must be printed before creating the relationship
Answer: B. The related fields need compatible data types, and the field on the parent side must be unique, normally a primary key. A common setup is an AutoNumber CustomerID primary key in CustomerT and a Number field, set to Long Integer, for CustomerID in ContactT.
If you got all five, congratulations. Your relationships are probably properly enforced and not wandering around in the rain looking for a missing parent record. If you missed a few, watch the embedded quiz for a quick review, and remember that relationships and referential integrity are some of the most important tools you have for keeping an Access database clean.
Live long and prosper,
RR
No comments:
Post a Comment