- Local time
- Today, 08:32
- Joined
- Feb 19, 2002
- Messages
- 42,970
I did tell you how to solve this problem (Cascade Update) and also that you should not use this type of changeable primary key. The other experts have also suggested that you use an autonumber primary key.At the moment if I change the stock number in the stock table, I lose all the Order Detail information in the Order Detail Table. Of course what I would like to happen is that I change the stock number in the stock table and all the stock numbers change in the Order Detail Table.
An example of how this helps you is if you want to change the "part Number" of a part, you just change it! NOTHING else changes because although there is a unique index on the part number to prevent adding duplicates, this is a human identifier and is not used for anything in the application except for searching and display.
Minty suggested a method of changing the primary key. There is no need to do that when all you need to do is to change the relationship to Cascade Update. However, if you are willing to take the time to change the PK (and it will not be an easy task at this time) then we can help you do it. Cascade Update will not help in this situation. It will have to be done all with update queries and once it's done, you would remove RI from the original fields and add it to the New, numeric PK and FK, and the final step would be to remove the old text PK and FK's.
People have a comfort level when they look at a code value and "know" what it is. In some cases I even develop these human readable codes but the reality is that the application does not care and you should not make it care by making these "human" codes a primary key. Surrogate keys (which is what autonumbers are) stand in for the unique identifier that the business prefers to see. The candidate key (what we call the unique business identifier) is almost always just a data field with a unique index to prevent duplicates but the surrogate key (autonumber) is used for all relationships with child tables. Users are given search options that will search their "human" key because they will probably never see nor do they have to see, the surrogate key that ties everything together behind the scenes.
Last edited: