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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

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.
1 Solution
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.
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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.
OctalysAuthor Commented:
thanks for the answers!

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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now