Solved Cascade Update and Cascade Delete

[Teri Bridges]

I think I get the basic idea, but I could use some guidance on these two features.
I am not sure when it is a good idea or a bad idea.

I need the user to be able to delete a record in the form view so I set my relationship to cascade delete. I hope this means all follow-on data is deleted as well.

For example Lesson>Topic>Event
If I delete the topic the event associated with that topic is deleted, but the lesson would not.

Any guidance or good foundational rules would be greatly appreciated.

 

[theDBguy]

Your interpretation is correct. However, I personally don't enable cascade delete. I would rather control how records are deleted, that is if I even allow deletion. Once a record is deleted, you won't be able to recover it, unless you have a backup. So, it's always advised to archive the data instead. On the other hand, I do enable cascade updates.

 

[Teri Bridges]

Your interpretation is correct. However, I personally don't enable cascade delete. I would rather control how records are deleted, that is if I even allow deletion. Once a record is deleted, you won't be able to recover it, unless you have a backup. So, it's always advised to archive the data instead. On the other hand, I do enable cascade updates.

I am not sure I fully understand the cascade updates. If I make a change to a record from a form it updates the records. Do you have an example of the cascade update you can share?

 

[theDBguy]

I am not sure I fully understand the cascade updates. If I make a change to a record from a form it updates the records. Do you have an example of the cascade update you can share?

Well, I should have really said I would be more willing or support enabling cascade updates than cascade deletes. If you use surrogate primary keys, like Autonumbers, cascade updates won't affect you much. Here's one potential example. Let's say you have a parent table with a natural primary key, as in.

ProjectsTable
ProjectCode (PK)
ProjectName
etc.

That table might contain something like:

ABCDE; Project1
HIJKL; Project2
QRSTU; Project3

Now, your database might also have a child table for the tasks associated with each project.

ProjectTasksTable
TaskID (PK)
ProjectCode (FK)
TaskDescription

It might contain something like:

1; ABCDE; Task1
2; ABCDE; Task2
3; HIJKL; Task1
4; HIJKL; Task2
5; HIJKL; Task3
6; QRSTU; Task1

The above is just a quick and simple example to, hopefully, explain how cascade updates work.

So, now, you know cascade delete works by removing all the tasks associated with the project code ABCDE, if you delete that record from the projects table. What cascade update will do is if instead you changed the project code from ABCDE to WXYZO, then all the tasks with a foreign key of ABCDE will also change/update to have an FK of WXYZO.

Does that help?

 

[Teri Bridges]

Well, I should have really said I would be more willing or support enabling cascade updates than cascade deletes. If you use surrogate primary keys, like Autonumbers, cascade updates won't affect you much. Here's one potential example. Let's say you have a parent table with a natural primary key, as in.

ProjectsTable
ProjectCode (PK)
ProjectName
etc.

That table might contain something like:

ABCDE; Project1
HIJKL; Project2
QRSTU; Project3

Now, your database might also have a child table for the tasks associated with each project.

ProjectTasksTable
TaskID (PK)
ProjectCode (FK)
TaskDescription

It might contain something like:

1; ABCDE; Task1
2; ABCDE; Task2
3; HIJKL; Task1
4; HIJKL; Task2
5; HIJKL; Task3
6; QRSTU; Task1

The above is just a quick and simple example to, hopefully, explain how cascade updates work.

So, now, you know cascade delete works by removing all the tasks associated with the project code ABCDE, if you delete that record from the projects table. What cascade update will do is if instead you changed the project code from ABCDE to WXYZO, then all the tasks with a foreign key of ABCDE will also change/update to have an FK of WXYZO.

Does that help?

Thank you, I get it now. Happy Dance!

 

[isladogs]

I'm a bit late but in case it helps, have a look at my article

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk

 

[ebs17]

Cascade update ... I've never used it. I think anyone who needs something like that has a lot of clutter in their database beforehand.

Deleting is not the typical case for a database with important data anyway. That's where you're more likely to collect.

 

[isladogs]

Cascade update ... I've never used it. I think anyone who needs something like that has a lot of clutter in their database beforehand.

To answer in your typical style, anyone who makes comments like that doesn't understand the purpose of cascade updates

 

[ebs17]

doesn't understand the purpose of cascade updates

That's right, in my case.
I find changing keys very problematic. Why not create these correctly in the first place?

 

[isladogs]

Please read my 3-part article linked in post #8. The relevant section is towards the end of part 1

 

[ebs17]

I can't find any interesting information.
In my opinion, with a surrogate key there is no reason to have to change it later.

 

[gemma-the-husky]

I am pretty sure I have never used a cascade operation. I just do not like the idea.

Let's say you have a customer account, and use an Alpha index.

Personally, I would use an autonumber PK for the customer table, and add an index on another field called customer_sort_key. So the users can have an autonumber PK field, and also have a customer_sort_key of ACME for the ACME Corporation. Now ACME Corporation gets taken over by Widget Company.. You can change the customer_sort_key to WIDGET, so users can obtain reports sorted differently. But the system uses the autonumber PK to relate orders or jobs, for instance, to the customer and never uses the real world sort key. So there never needs to be a cascade update operation.

I wouldn't cascade delete records either. I would rather tell users that they just cannot delete the ACME account because there are historical records using the account.

Now if they really want a process that deletes sales history, price history, and so on for ACME, I can develop one, but I would rather the client thinks very carefully about it first, and then I will add a process to manage the deletes.

 

[isladogs]

By chance Mike Wolfe wrote an article about this topic today:

Access 101: Referential Integrity

Referential integrity is the key to ensuring data quality in a relational database. Learn what it is and how to enforce it among your back-end tables.

nolongerset.com

 

[gemma-the-husky]

@Pat Hartman

Pat, I get what you're saying, and maybe I'm wrong, but I would rather manually control record deletion.

I don't make a changeable value as the PK. I said I would add another (indexed) field for values that have meaning but may need to change.

 

[isladogs]

FWIW I never use cascade update either as I use auto number PK fields. However, I do use cascade delete in cases where it does what I need with no additional code required