Cascade Updates ?

[(PeteCresswell)]

Is there any performance-related/technical reason not to just
enable this on all relationships unless there's an explicit
reason not to?

 

[Jerry Whittle]

If you are joining tables with natural primary keys (i.e. the key data has
meaning such as a name), cascade update can save you a lot of grief if, for
example, someone changes their name.

Now if you use artifical primary keys, such as an autonumber with no
"meaning", there really isn't a reason to use cascade update as you should
never have to change the primary key data in the parent table. BTW: I almost
always user autonumbers for the PK.

Cascade Delete? You better be very careful and very sure of the business
rules for that one. Once had to fix a mess where a guy decided to never, ever
do business with a certain customer again and deleted them. Cascade delete
took out all the history of sales for that customer too!

 

[(PeteCresswell)]

Per Jerry Whittle:

Now if you use artifical primary keys, such as an autonumber with no
"meaning", there really isn't a reason to use cascade update as you should
never have to change the primary key data in the parent table. BTW: I almost
always user autonumbers for the PK.

That's where I'm coming from except for some lookup tables where
I want an ID value of zero to represent "[NA]".

As in:

tlkpReferenceRateType
-----------------------------------
0 = Unknown
1 = Libor 3-month
2 = Libor 6=month
3 = Fed Fund Target.... and so-forth
-----------------------------------


Where I got caught was copying table values from another app and
deleting unused values - leaving gaps in the sequence.

The gaps got me when I was flipping the data on it's side with a
column for ID#1, a column for ID#2... and so forth. My code was
such that the missing values resulted in empty columns.

No big deal to change: I just enabled cascading updates in all
directions from the lookup table and changed the IDs as
desired...

But the process got me thinking about it and wondering if
cascading updates might be a good SOP when setting up tables in
general.

 

[Douglas J. Steele]

Per Jerry Whittle:

Now if you use artifical primary keys, such as an autonumber with no
"meaning", there really isn't a reason to use cascade update as you should
never have to change the primary key data in the parent table. BTW: I
almost
always user autonumbers for the PK.

That's where I'm coming from except for some lookup tables where
I want an ID value of zero to represent "[NA]".

As in:

tlkpReferenceRateType
-----------------------------------
0 = Unknown
1 = Libor 3-month
2 = Libor 6=month
3 = Fed Fund Target.... and so-forth
-----------------------------------


Where I got caught was copying table values from another app and
deleting unused values - leaving gaps in the sequence.

The gaps got me when I was flipping the data on it's side with a
column for ID#1, a column for ID#2... and so forth. My code was
such that the missing values resulted in empty columns.

No big deal to change: I just enabled cascading updates in all
directions from the lookup table and changed the IDs as
desired...

But the process got me thinking about it and wondering if
cascading updates might be a good SOP when setting up tables in
general.

Personally, I don't see the point. I've never once set Cascade Update in any
database that I've built.

My belief is that a key is (or should be) a permanent thing and should not
be changed.

You're describing a one-time occurrence. Would you have known to change the
numbers of the IDs you kept?

 

[(PeteCresswell)]

Per Douglas J. Steele:

Personally, I don't see the point. I've never once set Cascade Update in any
database that I've built.

My belief is that a key is (or should be) a permanent thing and should not
be changed.

Always seemed to me that if CascadeUpdate were needed, the design
of the DB wasn't sufficiently normal.

You're describing a one-time occurrence. Would you have known to change the
numbers of the IDs you kept?

I probably should have thought of that... but I shoulda' thought
of a *lot* of things.... -)

I don't have a lot riding on this. It's more idle speculation
than anything else. Sounds to me like I should continue with
CascadingUpdates=False as my default approach - for the reasons
you've stated plus the normalization thing.