Testing Cascading Deletion and Cascading Updates

When you delete a record in a primary or base table on which records in a related table depend, cascading deletion automatically deletes the dependent records. Similarly, if you modify the value of a table's primary-key field and a related table has records related by the primary-key field's value, cascading updates changes the value of the related foreign-key field for the related records to the new primary-key field value.

Cascading deletions and cascading updates are special types of action queries that the Jet engine executes for you. The following three sections show you how to use Jet's cascading deletion and cascading updates features with a set of test tables copied from the Orders and Order Details tables of Northwind.mdb.

Creating the Test Tables and Establishing Relationships

When experimenting with database features, you should work with test tables rather than live data. As mentioned in the note at the beginning of this chapter, using copied test tables is particularly advisable when the tables are participants in action queries. The remaining sections of this chapter use the two test tables, tblOrders and tblOrderDetails, that you created in preceding sections:

1. Open the tblOrders table in Table Design view.

2. Change the field data type of the OrderID field from AutoNumber to Number and make sure that the Field Size property is set to Long Integer. (This change is necessary to test cascading updates in the next section.)

3. Close tblOrders and save your changes.

Cascading deletions and updates require that you establish a default relationship between the primary and related tables, and enforce referential integrity. To add both cascading deletions and updates to the tblOrderDetails table, follow these steps:

1. If you haven't created tblOrderDetails, use the Clipboard to copy the Order Details table to tblOrderDetails.

2. Click the Relationships button to display the Relationships window.

3. Scroll right to an empty area of the Relationships window.

4. Click the toolbar's Show Table button to display the Add Table dialog. Alternatively, right-click the upper pane of the Query window and choose Show Table.

5. Double-click the tblOrders and tblOrderDetails items in the list, and then close the Show Table dialog.

6. Click and drag the OrderID field of tblOrders to the tblOrderDetails table's OrderID field to establish a one-to-many join on the OrderID field and open the Relationships dialog.

7. Mark the Enforce Referential Integrity check box, which enables the two cascade check boxes.

8. Mark the Cascade Update Related Fields and Cascade Delete Related Records check boxes, as shown in Figure 13.22.

   Figure 13.22. Add Jet's Cascade Update Related Fields and Cascade Delete Related Records features to automatically maintain the tblOrderDetails table's referential integrity.

   

9. Click Create to make your changes to the join effective and close the Relationships window. Click Yes when Access asks if you want to save your changes to the window's layout.

   

   If you receive an error message when you click the Create button, see the "Access Won't Create a Jet Relationship to a New Table" topic of the "Troubleshooting" section near the end of the chapter.

   
   

Testing Cascading Deletion

To try cascading deletion with the test tables, follow these steps:

1. Open the tblOrders and tblOrderDetails tables in Datasheet view.

2. Click the surface of the tblOrders datasheet to make it the active window and then click a record-selection button to pick an order in tblOrders to delete.

3. Press the Delete key to tentatively delete the selected records and the related order's line-item records in tblOrderDetails.

4. A message asks you to confirm the deletion. Click Yes to delete the records.

To verify that you've deleted the related records, you can scroll to the related record or records for the order that you deleted in the tblOrderDetails table. If you opened tblOrderDetails in step 1, the data cell values for the deleted related records are replaced with #Deleted. (These values aren't saved with the table.)

Testing Cascading Updates

Cascading updates to the foreign-key field of records that depend on a primary-key value that you want to change in a primary table is a valuable Jet feature. Performing updates of primary-key values while enforcing referential integrity is not a simple process; Chapter 5 briefly discusses the problems associated with performing such updates manually. To see how Jet takes the complexity out of cascading updates, follow these steps:

1. With the tblOrders and tblOrderDetails windows open, size and position the two datasheets as shown in Figure 13.23. Then click the surface of the tblOrders datasheet to make it the active window. Positioning the two table datasheet windows as shown in Figure 13.23 enables you to see the cascading updates in the tblOrderDetails window as they occur.

2. Change the value of the OrderID cell of the first record to the order number that you deleted in the preceding section. Alternatively, change the value of the OrderID cell to a value, such as 20000, that's outside the range of the values of the test table.

3. Move the cursor to another record to cause the cascading update to occur. You immediately see the changes in the OrderID foreign-key field of the related dependent records (see Figure 13.23).

   Figure 13.23. Changing the OrderID value in the base table automatically changes the OrderID values of related records, if you specify cascading updates.

   

No confirmation message appears when you execute a cascading update, because the effect is reversible. If you make an erroneous entry that causes an undesired cascading update, you can change the entry to its original value by reentering the original or the correct value manually.