Solved RI Cascade Updates

[LarryE]

When you do your RI Enforcements, do you ever check Cascade Updates?

 

[MajP]

Since I mainly use autonumbers and they cannot be changed, cascade updates has no meaning either way.
If I have a non autonumber key and those keys are natural keys or coming from some other source, then probably no. If the pk is changed then it was probably a mistake and not purposeful. I want to be alerted if children exist as a check.
If I control the keys then a change is likely purposeful and I would want to cascade.

 

[The_Doc_Man]

I've never checked anything but "enforce RI". I've never checked either cascade updates OR cascade deletes.

 

[LarryE]

Since I mainly use autonumbers and they cannot be changed, cascade updates has no meaning either way.
If I have a non autonumber key and those keys are natural keys or coming from some other source, then probably no. If the pk is changed then it was probably a mistake and not purposeful. I want to be alerted if children exist as a check.
If I control the keys then a change is likely purposeful and I would want to cascade.

Ok thanks. You just have to be careful when using that "feature".

 

[LarryE]

I've never checked anything but "enforce RI". I've never checked either cascade updates OR cascade deletes.

Thanks Doc. I discovered you have to be very careful using that.

 

[isladogs]

For more information on the purpose of cascade update and cascade delete, see 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

 

[CJ_London]

@Colin - your article doesn’t mention that you can edit tables from the relationships window - useful whilst creating a db structure for correcting, adding,deleting field names and other properties. Can’t create new tables though

 

[isladogs]

@Colin - your article doesn’t mention that you can edit tables from the relationships window - useful whilst creating a db structure for correcting, adding,deleting field names and other properties. Can’t create new tables though

Yes, you can both view / edit table designs and datasheets from the relationships window. I suppose it saves a click or two but offers no additional functionality beyond the same things from the nav pane.

Improvements to Query Designer, SQL View, Relationships window - Microsoft Support

support.microsoft.com

 

[Pat Hartman]

When you do your RI Enforcements, do you ever check Cascade Updates?

I always use autonumbers as PK's so Cascade Update has no meaning because of the way Combo and List boxes work. If I have a multi-field unique identifier (more frequent with junction tables), I never want to create a compound PK so I create a multi-field unique index and an autonumber as the PK and always use the autonumber for joins. When I have a unique identifier from a different application, I don't use it as my PK so I never have to worry about cascading a change. What I do is to create a separate unique index on the other system identifier.

I do frequently use Cascade Delete though. I prefer to allow the database engine to do the work for me and I have enough experience with RDBMS' to know which relationships would need Cascade Delete and which would NEVER need to cascade deletes. The relationships that should use Cascade Delete are the ones where the child records have no independent meaning of their own. Order Details is the common example. There is no relevance to these records without their parent Order Header.

 

[MajP]

Here is a real world where I use it. We have customer location codes for job sites. The customer defines them and they are guaranteed to be unique. They are ubiquitous in what we do referring to jobs and locations. Used in excel documents, Sharepoint lists, our ERP, our project management system so our access db uses these codes instead of an autonumber. They make nice PKs since they are 4 letter/number codes with no spaces or unique characters. A site may have several projects over its history and many other child relations. Every once in a while the customer will rename a site and it would require the users to do a lot of manual work to be able to rename and create all the child relations.

 

[LarryE]

I always use autonumbers as PK's so Cascade Update has no meaning because of the way Combo and List boxes work. If I have a multi-field unique identifier (more frequent with junction tables), I never want to create a compound PK so I create a multi-field unique index and an autonumber as the PK and always use the autonumber for joins. When I have a unique identifier from a different application, I don't use it as my PK so I never have to worry about cascading a change. What I do is to create a separate unique index on the other system identifier.

I do frequently use Cascade Delete though. I prefer to allow the database engine to do the work for me and I have enough experience with RDBMS' to know which relationships would need Cascade Delete and which would NEVER need to cascade deletes. The relationships that should use Cascade Delete are the ones where the child records have no independent meaning of their own. Order Details is the common example. There is no relevance to these records without their parent Order Header.

Yup. When you do the design, you need to check Cascade Deletes carefully. Some you do and some you don't.

 

[LarryE]

Thanks all. This ones done.