Changing key fields in a table with a one to many relationship 3

[snowyowl]

I have two tables in my database and they are set up as a one to many relationship. The parent is a machine table which stores basic information about the companies machine's. The child is a service job table which has a list of jobs that have been carried out on that machine. The machines keys are Customer number, Site of Customer and Machine ID. The Service job is all of the above plus a job Id. Referential integrity is set up as well.

My problem is when a machine changes site and it's site number needs changing. Because ref. integrity is set up, it does not allow me to change the number because records exist on the service job table that are related to it and vice versa. Is there any other way of altering these numbers appart from taking ref. integrity off (not an option on a live database), deleting the service job records. (again it's messy when recreating them from a holding place or creating dummy machine and service job records as holding places.

Any help would be greatly appreciated as this is something that will occur on this database quite often.

Thank you

 

[SBendBuckeye]

Go to your Relationships area, right click on the one in question between the 2 tables and choose edit relationship.
Make sure that Cascade Update Related Fields is checked. That should take care of it as long as you make the change on the one side of the relationship.

Good Luck!

 

[RickSpr]

Look at it this way: If Machine 123 can move from one site to another, then the site is not really part of the identity of the machine itself, and shouldn't be part of its primary key. Likewise, I would think, the Customer Number.

Probably all you should have in the primary key is the Machine ID. Then you could change the other columns without a problem.

But since this is a live database, you're probably not going to be able to redesign the table keys. What you need, then, is an Update Cascade rule. For Access, that's just a check box in the Relationship Properties window where you turned on referential integrity. With Update Cascade selected, a change to a primary key field is automatically propagated to all the foreign keys that refer to it.

Note, though, the effect this will have on the Service Jobs table. If you were using the Customer Number and Site in the Service Job table as information about where the job was performed, rather than just as part of the machine's identity, then after your update is propagated, these fields will no longer reflect the truth. That's one more indication that these fields should not be part of the foreign key, and hence not part of the machine's primary key.

If there is any way you can reduce the Machines table primary key to just MachineID, you should do that. When you recreate the relationship, Customer Number and Site will no longer be part of the foreign key in Service Jobs. If you don't need these columns in Service Jobs, delete them. If you do need them, as information about where the job was performed, retain them, and use code behind the form in which service jobs are entered to copy Customer Name and Site from the Machines table to the Service Jobs table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[SBendBuckeye]

Hello Rick,

Thanks for taking the time to post an excellent design analysis. It should be very helpful to others as well.

Have a great day and a star!

 

[RickSpr]

Buckeye,

As happens so often, we were both answering at the same time, i think. Only because you're not so long-winded as I am, you got yours posted first. Then when my answer gets posted, it looks like I can't leave well enough alone. I hope, as you say, somebody gets some benefit out of my analyses. I just can't seem to make myself give a brief answer, most of the time.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein