I want to have UNIQUE id's and dont want holes in my ID's (auto increment).

Posted on 2004-11-17
Last Modified: 2012-06-21

I have this problem, I have a table with a field 'id'.
'id' is an int is auto-increment indexed.

The order of data in my table isnt important, what is important is, that 'id' is unique (duh). And that no holes may exist. What is deleted must be filled to prevent holes in (id), because eventually every number(id) has to be used.

The thing I am designing has to work like this;

add data -> gets id (1)
add data -> gets id (2)
add data -> gets id (3)
delete row id(2)
add data -> gets id (2)

Anyone any idea's ? Been brainstorming for this one.
Question by:Octalys
    LVL 48

    Expert Comment

    LVL 26

    Expert Comment


    Author Comment

    I cant seem to find the solution on those 2 urls.

    I know about auto-increment, but auto-increment just add up every entry, and doesnt fill gaps.

    Author Comment

    ushastry, your link basicly says that I should handle it in code, without using auto-increment.
    But does that means its impossible to do what I want with only MySQL?

    I can always write functions to do this, but what I am really searching is a more MySQL solution.
    LVL 50

    Expert Comment

    AFAIK there is no inherent db function to do this...
    you'd need to use your own table to record the holes and re-use them as well as tracking the next key to use...

    i don't really understand why you are worried about "holes" anyway, that is just a display issue...
    and should be handled by the client app....
    i would suspect that if order is important to you then really you need to maintain history anyway , an therefor
    probably shouldn't allow Deletes in the first place...

    LVL 5

    Expert Comment

    Hi Octalys,

    I don't think that you are going to find an easy way around this. Essentially, you have a primary key column, which knows that it cannot store duplicate values, and then you have an autoincrmement property which is usually applied to this column, but does not actually need to be, and equally, can be applied to any other column. You don't have a "contiguous values" property.

    I think the best you'll manage is something like this:

    Select * FROM table WHER id <= 1000 ORDER BY id;
    Check number of rows returned. If == 1000, there are no holes, otherwise there are.
    Select * FROM table WHER id <= 500 ORDER BY id;
    Select * FROM table WHER id > 500 AND id <= 1000 ORDER BY id;
    etc. etc.

    i.e. you can perform a binary search to quickly find and plug holes, but you cannot easily prevent them.

    Hope that helps.

    Author Comment

    because its an invoice system, no gaps are allowed by the tax company..
    all invoice numbers must exist.

    But when an invoice is made, its not definite yet..  but an invoice number is already assigned to it.
    It happens that an invoice wouldnt get definite, thus deleted. So a new invoice need to replace that invoice Id.

    So it means I cant do it with MySQL? I know I could use functions in my app. to solve this, but I rather not.
    If it is the only way to do it, what functions do I need to make? How do I find gaps ?
    LVL 48

    Expert Comment

    Why not use a seperate table to get the invoice numbers and assign that number when needed.

    To get a new number for the tax use this:

    Select ID from SeqTable Where Name='invoice' for update;
    update SeqTable SET IF=ID+1 where Name='invoice2004';

    So you always get numbers without a gap and can easy change/reset the number on a per year base

    Author Comment


    Yes this is something I would have done, if temporily invoices doesnt exist. But temporily invoices do exist, and they do need to have their future invoiceId, and when they get confirmed, they would use the same invoiceId they were given to. And when they are confirmed they cant get deleted anymore, so only unconfirmed invoices can be deleted.

    So this wouldnt work.
    LVL 14

    Expert Comment

    it's possible and not so difficult :

    SELECT MIN( + 1) AS nextid
    FROM invoice AS i1 LEFT JOIN invoice AS i2 ON + 1 =
    LVL 8

    Expert Comment

    If you don't mind, you can use the old ISAM storage engine instead of the newer MyISAM, from what I recall, ISAM will reuse the holes in auto_increment, deleted numbers will be reused.
    The only thing is that ISAM has been deprecated and will be removed totally from v5 onwards.

    LVL 3

    Accepted Solution

    When you delete an invoice, record the id in a seperate table. When you create a new invoice, look in the table of deleted invoices, if there is data there, use the lowest number. If there is no data there, you have no deletes (therefore no holes) and let the auto-increment column do it's thing and assign you the next number.

    Author Comment

    thanks for the answers!

    eicheled answer is what I am looking for, just a simple solution!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.

    728 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

    17 Experts available now in Live!

    Get 1:1 Help Now