Setting Primary Keys for Product in Alphanumeric Form

[Spidey]

What is data integrity conflicts? And why it is best practice to prohibit changes to primary key fields? In my case, the primary key in my product master is alphanumeric and I need to delete or change the items sometimes because there are new items with same item number. I wonder if I should have product id and product number in the product master table so I can have two same item numbers, one has new packaging and another has old packaging.

Many thanks.

K. C.

[comment]

A primary key is supposed to be both unique and permanent.

If it's not unique, then a foreign key in a child table will be pointing to more than one parent.

If it's changed, then child records will become orphans.

---

BTW, this is not a "security concept".

Edited October 19, 2011 by comment

[Vaughan]

Imagine a baggage check in: you get given a numbered ticket, and your luggage gets the same ticket.

Now imagine that somebody else got the same number as yours; or that your number got changed but the ones on your luggage did not.

[comment]

:goodpost:

[Vaughan]

I'm working with one client who spent a couple of days changing all the "company codes" so they looked nicer. They were used as the primary keys. :-)

[BrentHedden]

:doah: