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


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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshMySQL Principle Technical Support EngineerCommented:
OctalysAuthor Commented:
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.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

OctalysAuthor Commented:
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.
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...

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.
OctalysAuthor Commented:
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 ?
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
OctalysAuthor Commented:

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.
it's possible and not so difficult :

SELECT MIN(i1.id + 1) AS nextid
FROM invoice AS i1 LEFT JOIN invoice AS i2 ON i1.id + 1 = i2.id
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.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OctalysAuthor Commented:
thanks for the answers!

eicheled answer is what I am looking for, just a simple solution!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

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.