Relationships are one of the things that keep an Access database from turning into a warp core-level mess. This quiz covers cascade updates, cascade deletes, relationship cleanup, the Relationships window, and subdatasheets. See how many you get right before checking the answers.
These are expert-level relationship questions, but the underlying ideas are important for any Access developer. Cascade delete can be useful, but it is not something you should turn on everywhere just because Access gives you a checkbox. That checkbox can delete a whole lot more than you intended.
{{YOUTUBE_EMBED_PLACEHOLDER}}Question 1: What does cascade update related fields do in a relationship?
A. Updates every field in the related table.
B. Updates matching foreign key values when the parent key changes.
C. Creates a new child record whenever a parent changes.
D. Changes AutoNumber values into Short Text.
Answer: B. Cascade update updates matching foreign key values in the child table when the parent key changes. For example, if you have a parent record with a manually assigned key and that key changes, Access can automatically update the matching foreign key values in related records.
In practice, I almost never use cascade update because well-designed tables usually use an AutoNumber primary key, and AutoNumber values should not be changed. Still, it can be useful occasionally when you are using a natural key or another editable value as the parent key.
Question 2: Which relationship is usually the best candidate for cascade delete related records?
A. CustomerT to OrderT, so accounting history disappears with the customer.
B. EmployeeT to PayrollT, so payroll history is erased when an employee leaves.
C. OrderT to OrderDetailT, so an abandoned order removes its line items.
D. CustomerT to ContactT because every related table should always cascade delete.
Answer: C. An OrderT to OrderDetailT relationship is a classic example of when cascade delete can make sense. Order detail records have no real purpose without their parent order. If you delete a temporary, test, or abandoned order, deleting its line items along with it is usually exactly what you want.
On the other hand, be very careful with customers, orders, employees, payroll records, invoices, payments, and anything involving business history. Deleting a customer should not automatically erase their order history. Deleting an employee should definitely not make payroll history vanish into the Holodeck. In many cases, it is better to mark records inactive rather than delete them at all.
Question 3: What must happen before you can delete a table that participates in a saved Access relationship?
A. The relationship must be removed first, either manually or through Access's prompt.
B. The table's primary key must be changed to an AutoNumber.
C. Every query that uses the table must be converted to a report.
D. Referential integrity must be turned on for every other table.
Answer: A. Access will not let you casually delete a table that is part of a saved relationship. You need to remove the relationship first. Depending on what you do, Access may prompt you to remove the relationship, or you can open the Relationships window and delete the relationship line yourself.
This protection is a good thing. Without it, you could delete a table and leave the rest of your database pointing into empty space.
Question 4: In the Relationships window, what happens if you remove a table box from the layout without deleting its relationship line?
A. The underlying table is deleted from the database.
B. Referential integrity is automatically disabled.
C. All related child records become orphaned.
D. The table is only hidden from that window; the relationship still exists.
Answer: D. The Relationships window is partly a layout tool. Removing a table box from the window does not delete the actual table, disable referential integrity, or remove the relationship. It simply hides that table from the current relationship layout.
If you want to remove the actual relationship, delete the relationship line. If you just want to make the diagram less cluttered, removing the table box is perfectly fine. Think of it as cleaning up your map, not demolishing the building.
Question 5: CustomerT has related ContactT and OrderT records. You want its Datasheet view to show a custom query containing only selected contact fields as the subdatasheet. What should you configure?
A. Set CustomerT's subdatasheet name to the custom query and specify the master and child fields.
B. Change CustomerT's primary key to match the query name.
C. Turn on cascade delete related records for ContactT.
D. Create an AutoNumber field in the custom query.
Answer: A. You can use a query as a subdatasheet instead of displaying the entire related table. Set the parent table's Subdatasheet Name property to your query, then specify the appropriate Link Master Fields and Link Child Fields.
That gives you control over what users see when they expand a customer record in Datasheet view. Instead of showing every field in ContactT, you can show only the useful contact information. Just make sure the query includes the field needed to link it back to the parent customer record.
The big takeaway is that cascade delete is best reserved for true dependent records, such as order details belonging to an order. Do not use it as a shortcut for cleaning up important historical data. Referential integrity should protect your data, not help it disappear faster.
If you want to see these relationship settings and examples in action, watch the embedded video. It covers the answers and the reasoning behind each one.
Live long and prosper,
RR
No comments:
Post a Comment