Database Relationships  «Prev  Next»

Lesson 6 Using Cascade Update and Cascade Delete options
Objective Set the Cascade Update and Cascade Delete options for relationships.

Using Cascade Update, Cascade Delete Options

It would be very hard to use Access with referential integrity in place if you did not have any ability to delete and update records in tables where there are other related records. There are some cases where you will definitely want to make sure records in tables cannot be deleted for historical purposes. However, there may be some cases where you do not care whether records are deleted, as long as the related records are deleted or updated to reflect the change. Using Cascade Update and Cascade Delete allows you to specify that you want records to be updated and deleted, and is really quite straightforward. If you look at the Edit Relationships dialog by double-clicking a join line for a relationship, you will see the options below the Referential Integrity check box, displayed below:

Edit Relationship options including 1) Enforce Referential Integrity 2) Cascade Update Related Fields

Once the options have been chosen, they will affect your database by allowing you to update the one side of a one-to-many relationship, and have it reflected in related tables, as well as delete all related records in related tables. Here is more information about the individual options.

Cascade Update

This option is useful mainly when you have a primary key field that can be modified. If Cascade Update is set to Yes, then when that value is modified, all the records related have the value modified as well.

Cascade Delete

Cascade Delete causes any records in related tables to be deleted along with the deletion of the current record. For example, if you were to delete a record in the Clients table, you would also be deleting any records in the Projects table with that particular ClientNo.
Note: If any of the tables related to Projects do not have Cascade Delete set to Yes, they will receive the referential integrity error message that you are not able to delete or change the record.
Because you will undoubtedly use the Cascade Delete option to a greater extent than Cascade Update, work with this simulation to get more practice.
Using Cascade Delete
The next lesson is the module wrap-up. You will get a brief review of the topics you have covered in this module. Also, you can take a quiz that will help you verify what you have learned and discover topics that you might want to review in a more detailed manner.