I am fairly new to database development so I have a conceptual question.
I have a database which is tracking chemicals. In the Inventory table the Primary Key is defined as an integer which is set as an identity with seed and increment 1.
I am using this field not only as a unique identifier but it is also the barcode for the printed labels we have placed on each of our chemicals.
I was looking into resetting indexes for another SQL project and came upon a statement that identities would be reordered when a truncate is performed on a database. If this happened to my chemical inventory it would be disastrous as my printed barcodes would no longer match the database (there are gaps where chemicals have been deleted).
So my questions are:
Have I made a basic design flaw that I need to fix?
Can I make a field that auto-populates with unique integers but does not run the risk of reordering?
Am I worrying about nothing ;)?
Thanks for your help.