UPDATE query - Cascade Updates?!

[kdm3]

UPDATE queries scare me, there's too much scope for causing huge problems. I find myself with a requiremnt to use one however, and was hoping someone could help me with the following:

I have two linked tables, one with a single primary key, one with a composite primary key - one half of with is the primary key of the other table...

Table 1
ID*

Table 2
ID*
IDItem*

I want to alter the ID field once a certain criteria is met. I intend using an UPDATE query, but found that it only changed the values in one table.
I was wondering if there was a way to cascade update, so that all linked fields would be changed together, or whether the UPDATE query would have to be written so that it changes any vaues which require changing explicitly?

The thing which scares me most is losing referential integrity, so I'd appreciate some advice.

 

[Len Boorman]

You can set up the relationship between the two tables in the Relationship display. You can set cascade update at this point.

So Update Query on PK cascade update runs from Primary Key to Foreign key.

Ref Int would then also be maintained

Take a copy of database, Do the changes and see results. Then when you are happy and condident do it for real

Len B

 

[kdm3]

Most appreciated

Thanks Len, I sorted the problem out pretty quickly in the light of what you said, but as Pat points out, it probably wasn't the best idea to consider changing the primary keys.

To put you in the picture of what I'm doing - I basically have a table holding info about material requirements... when an order for these items is made the status then changes, to being on order - to prevent from ordering the same thing twice or more!? I had envisioned just changing the PK from e.g. REQ01 to ORD01, but on second thought this isn't the best idea - so I shall have seperate tables for the Orders and Requirements - and then either delete, or just deactivate the requirement once an order is placed, and copy the info across to the order table.
(The two linked tables originate due to the fact that info is kept about the order as a whole, supplier etc. - but also about each item in the order itemId etc.)

 

[Len Boorman]

Most important not to attempt to use Primary Keys for the wrong purposes.

Sometimes it is necessary to change PK values but there should be a good reason for this. In the aerospace industry an aircraft may have a development designation and then a final designation. Under these circumstances a change of value may be required.

If in doubt this forum is very good at answering questions.

Len

 

[help123]

does anyone know the sql code for this?

 

[vbaInet]

A rather old post. 2004