Edit the ItemCode as Primary key, of Inventory (1 Viewer)

hfsitumo2001

Member
Local time
Today, 05:53
Joined
Jan 17, 2021
Messages
365
Hello, I just suppose a user make a new itemcode(in inventory table itemcode is a primary key), say "40x42C", that should have been "40x42B", then the user already make a receipt transaction for this item code. The table below is the db relationship. When I tested to change it to "40X42B" , an error message saying " the change can not be changed, because it is being used in Purchase Detail/Sales details (see picture below).

My question is that the behavior of the primary key?, Probably we can change item code, when we created, but have never been used yet for transaction?. Thank you for any idea, because I have to make a presumption so that I know how to handle it.
Relatioship.jpg
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:53
Joined
Oct 29, 2018
Messages
21,357
Make sure you have a backup and then try using Cascade Update.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:53
Joined
May 7, 2009
Messages
19,169
it's Unlikely situation, when there is already a PO and Receipt?
Item code are created by top management, not by user level.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:53
Joined
Feb 19, 2002
Messages
42,970
1. Using ID as the name of the PK just leads to confusion.
2. The FK named PODetailsID is misleading. It sounds like an autonumber for the Details table but in fact it is the FK to the PO Table. A more rational name would be just POID.
3. Although multi-field PKs are allowed, I found with Access that it was better to stick with straight autonumbers as the PKs and make unique indexes to support business rules. So, PODetailsID would be the PK and POID would be the FK to the PO table and there would be a unique index on POID + ItemCode.

And finally, it is ALWAYS better to use an autonumber than a meaningful key for just the reason you are here. If you had used ItemID as the PK and ItemCode was just a data field, you would simply change the invalid value and move on.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Sep 12, 2006
Messages
15,613
it's Unlikely situation, when there is already a PO and Receipt?
Item code are created by top management, not by user level.

I don't know about that. In my experience, top management leave details to the troops, and the end result is often duplicatio, effectively by "near" duplication.

Product names with variations such as mm, MM, mm., MM. and so on, because it's easier to let user create the items they need than to set up a system for careful management of the items.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:53
Joined
May 7, 2009
Messages
19,169
don't know about that. In my experience, top management leave details to the troops, and the end result is often duplicatio, effectively by "near" duplication.
then you have poor management.
on my experience, there is first "material take-off", before anything else.
 

hfsitumo2001

Member
Local time
Today, 05:53
Joined
Jan 17, 2021
Messages
365
it's Unlikely situation, when there is already a PO and Receipt?
Item code are created by top management, not by user level.
Ok, thank you Arnel, we are not a big company, we are just school's housekeeping dept. So actually it is ok, I have made it, and I think it is very rare to revise the item code
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:53
Joined
Feb 19, 2002
Messages
42,970
Even if it is rare to revise the item code, it is poor practice to make anything that can be revised the PK.
 

Users who are viewing this thread

Top Bottom