[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Identities vs auto-numbering

Posted on 2007-10-18
Medium Priority
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

by:Chris Mangus
ID: 20103070
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
ID: 20103084
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

Aneesh Retnakaran earned 1000 total points
ID: 20103086
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

ID: 20103200
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline

872 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