Cascade Update

[Renee]

I have several tables linked by Social Security No.(the
primary key). They also all contain each employee's
initials.

Employee_Info (my main table) is linked one-to-one with
Phone_Numbers, which is linked one-to-one with Addresses,
etc.

All the relationships are supposed to enforce referential
integrity and cascade update. But if I change the Social
Security No. in one place I get an error saying that the
record cannot be saved because there is not a
corresponding record in the related table. I can change
the initials in one table, but it does not update the
related tables.

Any ideas?

 

[John Vinson]

I have several tables linked by Social Security No.(the
primary key). They also all contain each employee's
initials.

Why? What's the point of storing the initials? Who's RD anyway - you,
or Ralph Dunham, or Ruth Dworshak? And what happens if Ruth marries
and changes her name: you now have a whole bunch of RD's in the table
that need to be changed - but finding them is complicated?

Don't store data redundantly. Store it ONCE, and then link to it by
some unique, stable, short key; if you trust SSN's (not everyone has
one, and they are not solidly guaranteed unique) just store the SSN
and LOOK UP the name. Generate the initials on the fly.

Employee_Info (my main table) is linked one-to-one with
Phone_Numbers, which is linked one-to-one with Addresses,
etc.

So every person in the table has only one phone number (I have three)
and only one address? (I have two). One to one relationships are QUITE
rare. Are you sure you don't want one to many?

All the relationships are supposed to enforce referential
integrity and cascade update. But if I change the Social
Security No. in one place I get an error saying that the
record cannot be saved because there is not a
corresponding record in the related table. I can change
the initials in one table, but it does not update the
related tables.

Upon which field are the tables joined? SSN, Autonumber, initials, or
what? Cascade Updates will update ONLY the joining field, not any
other fields, and will only cascade from the "master" table into the
"child" tables, not the other way; and it is actually rarely used,
since ideally Primary Key values are stable and should never or very
rarely change - so it should never or very rarely be necessary to
cascade those changes.

 

[Sam]

I don't understand how you're using the intials as you say that SSN is the
PK in your Employee_Info table and also the FK in the other tables. I'll
ignore the initials stuff!!!

If you wish to cascade update the SSN then that can only happen from the
Employee_Info table, so on all forms make sure it is the Employee_Info SSN
field that is used, NOT the SSN from one of the linked tables. When changed
it will be updated in the linked tables.

HTH
Sam

 

[Renee]

My cascade update on the primary key now works since I
made sure it would only be changed through the main table.

Now when I change the primary key I get a message saying
that this record has been changed by another user since I
started editing it. I am the only user so I know that's
not right. If I click Save Record in the message box, it
evidently will save without any problem.

I would like to know how to avoid that message.

Thanks.
Renee

 

[Sam]

Renee,

How are you changing the primary key? I suspect you're changing it twice
somehow, and it has not yet been saved when the second change is attempted.

HTH
Sam