XRCC
asked on
Identities vs auto-numbering
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.
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.
usually bar codes are more complex than autonumbering, even more if them will be used commercially, where you have to follow some industry rules for numbering.
But even if for internal use, autonumbering makes no much sense, you can use some kind of inteligent code with division, by example:
xxyyyyz, where xx = product group, yyyy = product itself, z = product hazard
still you can preserve you autonumbering code as primary key, this will save some space when relating with other tables, but as I stated, it is not a good idea to use as bar codes.
But even if for internal use, autonumbering makes no much sense, you can use some kind of inteligent code with division, by example:
xxyyyyz, where xx = product group, yyyy = product itself, z = product hazard
still you can preserve you autonumbering code as primary key, this will save some space when relating with other tables, but as I stated, it is not a good idea to use as bar codes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I should clarify:
I am not expecting a truncate nor am I planning to perform one. My concern was more to the possibility of this field getting renumbered by some feature that I am not aware of. The truncate was just an example that stumbled upon which is why I used it.
The barcodes are internal only and have been made unique to avoid conflicts with commercial codes. There is no need to make the codes meaningful as they are not human readable and they link back to the database which contains all the chemical information.
The system is up and running and works great ... I just had this concern about the renumbering and figured I'd throw it out there.
Again, thanks for all the comments. I believe I will not make any changes at this time.
I am not expecting a truncate nor am I planning to perform one. My concern was more to the possibility of this field getting renumbered by some feature that I am not aware of. The truncate was just an example that stumbled upon which is why I used it.
The barcodes are internal only and have been made unique to avoid conflicts with commercial codes. There is no need to make the codes meaningful as they are not human readable and they link back to the database which contains all the chemical information.
The system is up and running and works great ... I just had this concern about the renumbering and figured I'd throw it out there.
Again, thanks for all the comments. I believe I will not make any changes at this time.
For what reason are you expecting a truncate?
In general, it's better to use a natural key for your PK vs. an artificial key, like an identity field.
You could set up a routine to generate your own unique integers but it likely wouldn't auto-populate as an identity field does. Perhaps you could set up an integer field. You could then put an INSTEAD OF INSERT trigger on the table that would calculate the MAX value you're used prior in this field and then insert the data with that value, plus one, in the integer field.