Shared Primary Key not updated between mulitple tables.

I have what I hope is a simple question.

I have a simple database with three tables.  Each table has a primary key of "name".  Other information is not shared across the databases.  I have referential integrity and cascading enforced.  When I update the "name" field in one table, the "name" fields in the other tables are not updated.  Suggestions?

Thanks!!
LVL 2
meade470Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GRayLCommented:
Name is an Access reserved word and should not be used as a table, field, or variable name.  Only one of the tables with name can be designated as a primary key.  The other two tables have to have nameid as a foreign keys.
thenelsonCommented:
Cascading can be set only for additions and deletions of record, not for changing the records.  You would have to do this yourself with after update and an update query.  But more important do not use real useful information for your referential key fields for exactly this reason and others!  If your users are going to see and use "name", create another field that can be an autonumber field in one table and number field of long type in the other tables for the referencing.  Don't let your users be able to change these numbers (ususally don't let them see it either - but the only problem with that is that someone will ask you if they can change that number, to which you would answer no.)
GRayLCommented:
That is to say you have used therelationships tab to build the relation between the tables.
indexed the two foreign keys, and set cascading on here. I think it has something to do with not being able to update a primary key via a query, which is essentially how Access would handle the cascading.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

meade470Author Commented:
Can't I have a database in which a "shared" field can be updated by making changes to a single table?  Does this require an update query?
NatchiketCommented:
that's right grayl cascade updates would only be propogated on foreign keys.
I guess the question is why has meade470 chosen to struture the database in this way
NatchiketCommented:
meade

The purpose of propogating changes on related fields is in order to maintain referential integrity between tables in a database
it will only work in the situation where there is a parent table and a child table with the parent table having the field in question as a primary key and the child table's  field is a foreign key.

It's a questionable practice anyway, the idea behind primary keys is that they provide a way of uniquely identifying each record in a table, as such they should generally remain static.

In your current structure you will need to run some code which updates the fields on the orther tables after the name field is changed in one table.  To do this you would have to have some overlaying structure like a form which can respond to events such as changing a field value and then fire off the appropriate queries to update the other tables

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.