Cascading updates in Jet relations

Jet, your default database engine for Access, offers an option to turn on cascading updates when creating a relation object, often referred to as a relationship during database discussions. On first blush, this can seem like a good idea in many cases. I’ll discuss briefly here why that is not true. I’ll reserve comments on cascading deletes for another column.

Cascading updates allow you to change the primary key in the parent (“one”) table in a relationship and have all matching values in the child (“many”) table automatically and silently update themselves. There are two distinct situations that might occur: 1) the relation is on a text field code and you want to modify the code because of spelling errors or business rule changes, or 2) the relation is on a numeric field with the number’s meaning stored in a second column of the parent table in the relationship. Both of these are common occurrences where a lookup table (the parent) feeds a primary table (the child) in your database. Updating a value in a text-text relationship allows the user or database administrator to adjust the code or fix a misspelling and have all the changes propagate to all child tables. Pretty cool. It seems you’ve protected yourself from typos and code changes due to business considerations. Updating a value in a numeric-numeric relationship allows the database administrator to, for example, rearrange the lookup table so the default sort order (always determined by the primary key in Jet) becomes some arbitrary sequence, or maybe because he wants to group similar values based upon their proximity within the primary key sequence (ugh!). Cascading updates do seem to be useful.

Turn off cascading updates!

Now the bad news. Turn off your cascading updates except in very special and well-considered circumstances. The foremost reason is that once you used any of the parent table’s primary key values as criteria in an Access query or in your VBA code, changing those values in that table will break everything in your database that is based upon the changed value. This is no small problem.

Are there exceptions? Certainly. If the primary key of the parent table is unlikely to be used as a built-in criterion in a permanent Access query, you may be safe to turn on cascading updates. This may be a poor example, but if you link a parent and child table by Social Security number, you may be safe with cascading updates. It’s very unlikely you will build a permanent query with specific Social Security numbers as criteria. So, use cascading updates sparingly, if at all. I never turn them on by default, and they only occur in a very small percentage of all relations that I establish in my databases.

Until next time, happy computing!