This chapter contained an example of Cascade Delete Related Records using three tables in the Northwind database. The example was complicated, so instead of having you perform the steps, I gave you a narrative. Now that you understand how Cascade Delete Related Records works, you might like some hands-on experience in this area:
In the copy of the Northwind database you made in the "Cascade Update Related Fields" section, choose Tools, Relationships to display the Relationships window. (If you haven't done that exercise, follow the instructions in step 1 to make a copy of Northwind.mdb.)
Note that there is a one-to-many relationship between Categories and Products, as well as a one-to-many relationship between Products and Order Details.
Double-click the line between Categories and Products.
Select Cascade Delete Related Records. Click OK.
Double-click the line between Products and Order Details. In the Edit Relationships dialog box, note that referential integrity is enforced, but the Cascade Delete Related Records is not selected.
Click Cancel. Minimize the Relationships window.
If necessary, maximize the Database window. Open the Products table.
Select the Category column. Click the A-Z button for an Ascending Sort. Note that several products are beverages.
Close the Products table. Click Yes to save your design changes.
In the Database window, open the Categories table.
Attempt to delete the Beverages record. Access won't let you delete the record because there are related records in the Order Details table. Categories and Order Details do not have a direct relationship. But Products and Order Details do, and Cascade Delete Related Records is not implemented. Thus, you cannot delete a record in the Categories table that has related records in the Products table because the Products table has related records in the Order Details table and referential integrity is enforced.
Press Escape and close the Categories table.
Maximize the Relationships window.
Right-click the line between Products and Order Details, and select Edit Relationship.
This is another way of opening the Edit Relationships dialog box.
Select Cascade Delete Related Records and click OK.
Close the Relationships window. If you're asked to save the layout of the Relationships window, click Yes.
Open the Order Details table. Select the Product field. Click the A-Z button for an ascending sort.
Scroll down to note that many orders are for Chai, a beverage.
Close the Order Details table. Click Yes to save your design changes.
Open the Categories table.
Delete the Beverages record.
Access warns you that you are about to delete one record as well as records in related tables.
Click Yes to confirm the delete.
Close the Categories table and open the Products table. Note that all the products with the category Beverages have been deleted.
Close the Products table and open the Order Details table. Scroll down to note that all the records with Chai have been deleted. Close the Order Details table and close the database.