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.
Who is Participating?
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
truncate will removes all the contents of a table. Also, if you set any foreign key relation key relationships, then you wont be able to truncate the table. So I dont think any issues with your present design.
Chris MangusDatabase AdministratorCommented:
If you did a truncate, you'd lose all your data.  So, yes, if you reinput all your chemicals there is no guarantee you'd get the same identity value.

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.
Jaime OlivaresSoftware ArchitectCommented:
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.
XRCCAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.