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

Have a look at
http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html
and
http://dev.mysql.com/doc/mysql/en/InnoDB_auto-increment_column.html

also take a look at this..

http://archives.neohapsis.com/archives/mysql/2004-q2/4297.html

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.

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


hth

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.

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

hernst42,

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
WHERE i2.id IS NULL

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.

thanks for the answers!

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