Identities vs auto-numbering

Posted on 2007-10-18
Last Modified: 2010-03-19
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.
Question by:XRCC
    LVL 17

    Expert Comment

    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.
    LVL 55

    Expert Comment

    by:Jaime Olivares
    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.
    LVL 75

    Accepted Solution

    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.

    Author Comment

    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.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now