Share this Page URL

Chapter 3. Managing the Consistency, For... > Cascade Update and Cascade Delete - Pg. 77

Managing the Consistency, Format, and Integrity of Your Data 77 In cases like these, you can use mechanisms known as cascade update and cascade delete with referential integrity and perform a greater range of operations on your data. If you select Cascade Update Related Fields as part of enforcing referential integrity, any time you change the primary key of a record in the primary table, Access updates the value of the related foreign key in related tables. (If the primary key in the primary table is an AutoNumber field, the Cascade Update Related Fields option has no effect, because the value of an AutoNumber field can't be updated.) Selecting Cascade Delete Related Records causes Access to delete related records in a related table when you delete records in the primary table. Use Cascade Delete Related Records cautiously You can't undo the cascading deletion of records, so be careful about which relationships you apply this option to. If you delete a record from a form or in Datasheet view, Access displays a message indicating that related records will also be deleted. If you use a query designed to delete records, Access deletes related records without a warning. (Queries designed to delete records are known as delete queries; you'll learn about using delete queries and other action queries in,Chapter 9, "Extending Your Use of Queries.") We'll use the Customers and Orders tables in the HelloWorld3 database to demonstrate how to set up referential integrity with cascading updates and cascading deletions. Here are the steps to follow. Set up cascading updates and cascading deletions 1. 2. On the Tools menu, click Relationships. In the Relationships window, right-click the relationship line connecting the Customers table with the Orders table, and then click Edit Relationship. (You might need to change the layout of the tables in the Relationships window to see the relationship line clearly.) In the Edit Relationships dialog box, select Enforce Referential Integrity and also Cascade Update Related Fields and Cascade Delete Related Records, as shown here: 3. 4. 5. 6. Click OK in the Edit Relationships dialog box, and then close the Relationships window. As a quick test of the effect of selecting the option to cascade updates to records, open the Customers table again, and change the customer ID for Alfreds Futterkiste from ALFKI to ALFST. Close the Customers table. You don't receive an error message this time. Access has also updated the customer ID for all related records.