Casading Update

[edisonl via AccessMonster.com]

Hi,

Got a little issue here with Data Update:

If I change Primary Key information in the main table, How do other table
follow suit Eg:
Table 1 User ID 1111 Change to 2222
Table 2 User ID 1111 Capable of 2222 ?

Problem1: Certain Table use UserID as Primary Key (No Duplication), other
Table not using UserID
as Primary UserID.

Problem2: If I made changes now (Maybe Using Relationship or cascading), and
in the future decide to delete away the cascading function or relationship
which ever method being used will there be any implications in Data integrity
?

Regards, Edison

 

[John W. Vinson]

Hi,

Got a little issue here with Data Update:

If I change Primary Key information in the main table, How do other table
follow suit Eg:
Table 1 User ID 1111 Change to 2222
Table 2 User ID 1111 Capable of 2222 ?

If you have cascade updates set, this will happen automatically. Changing 1111
to 2222 in Table1 will cause the same change in Table2.

Problem1: Certain Table use UserID as Primary Key (No Duplication), other
Table not using UserID
as Primary UserID.

Doesn't matter. If UserID is a foreign key with referential integrity
enforced, cascade updates set, it will change every record in the child table
which has 1111 - whether there's one or a hundred of them.

Problem2: If I made changes now (Maybe Using Relationship or cascading), and
in the future decide to delete away the cascading function or relationship
which ever method being used will there be any implications in Data integrity
?

If you turn off Cascade, no harm will be done - you just won't be able to edit
the primary key field in Table1 if there are any matching records in Table2.

If you remove the relationship then yes, you'll destroy data integrity - you
will have no way to prevent adding orphan records to Table2.

I'd try to avoid either cascade updates OR cascade deletes unless you have a
very good reason to include them. One of the characteristics of a good
candidate primary key is that it is *stable* - a field which is frequently
changed is not a good choice! I've used cascade updates, but rarely, and
usually only temporarily in the process of fixing up a misdesigned database
(misdesigned by me in the two cases I remember).