changing the primary key of a table

[mihabaki]

HI

I did some BAD planning before I started creating my database and I set a text field ("field1") of a table ("table1") as primary key, then I linked this field to a field in another table ("table2", one-to-many relationship). Now I wan't to change some values in table1 in "field1" defined as primary, but I can't (Access refuses to do so...).

Because I have a lot of data entered allready I would like to know if there is any way for me to change values in "table1"/"field1" and that all the linked fields in other tables ("table2") would change correspondingly??????

I hope my question is clear enough to understand.

thanx in advance

 

[The_Doc_Man]

Alternative A:

1. Disable referential integrity based on that key.
2. Make two update queries.
2.a. One changes all FK values in table2.
2.b. Other changes all PK values in table 1.
3. Enable RI again.

Alternative B:

1. Make a COPY of the table1 record with the new values for the PK. I.e. Append new records for the new PK values.
2. Make a query to change the FK in table2 to the records having the new keys.
3. Make a query to DELETE the (now childless) records in table1

 

[The_Doc_Man]

Forgot about Cascade Update, but Pat is right.

She almost always is, though.

 

[mihabaki]

thanky you very much

both answers were very helpful

m.