Edit primary key values

[GPE]

Hi all,
We have a multi-user database where the primary key is set to a meaningful
text field. For many years, we have been happy with this and we never
expected that the primary key values would have to be changed. Now however,
there have been changes in gazetteer codes and as a consequence, the primary
key values in the main table and a few related tables in our database will
have to be changed. My question is: is it OK to change these values with a
series of update queries while keeping the primary key set on those table
columns? (To avoid problems, we will run the update queries when no one else
is logged onto the database, and after a backup of course.) Or should I
temporarily take out the primary key and set it again after the updating is
done? Has anyone else experience on this kind of tasks?
Thanks

 

[Allen Browne]

Simplest way to do this would be with cascading updates, so Access
automatically makes the change in the related tables if you change it in the
main table.

In the Relationships window, double-click the line joining the main table to
the related table.
Access pops up the Edit Relationships dialog.
Check the box for Cascading updates.
Repeat for the other relationships.

Now just change the text value in the main table, and Access does the rest
for you.

 

[GPE]

The problem is not in creating the queries. I plan to create a form where
users can fill the new gazetteer code, and then run the update queries in the
background.
What I am wondering is whether the updating of primary key values will cause
corruption of the tables, as we will have to update thousands of values over
several tables. Will it cause Access to crash or is it OK to do update
queries on the primary key column?

 

[Allen Browne]

The important question is, "Have you created relationships between your
tables, with Referential Integrity enforced?"

Your answer is:

a) No: Then RUN and do it now. It will solve your problems.

b) Yes: Then forget the update queries, and use cascading updates as
explained earlier.

c) I don't know: Then here's an introduction:
http://allenbrowne.com/xbase-07.html

Any other approach is a waste of effort, and guarantees you will have bad
data at some point.

Using cascading updates will not corrupt your database.