mysql find smallest + unique id available

Question

i have a column ID and something like 1000 items, some of then were removed like id=90, id=127, id=326

how can i make a query to look for those available ids, so i can reuse then for another item?

its like a min(ID) but i want to find only the ids that are NOT in my database, so if i remove a item with the ID = 90, next time i click on ADD ITEM i would insert it as id = 90

Backhanded

Answer 1

You can get the minimum available ID using this query:

SELECT MIN(t1.ID + 1) AS nextID
FROM tablename t1
   LEFT JOIN tablename t2
       ON t1.ID + 1 = t2.ID
WHERE t2.ID IS NULL

What it does is that it joins the table with itself and checks whether the min+1 ID is null or not. If it's null, then that ID is available. Suppose you have the table where ID are:
1
2
5
6

Then, this query will give you result as 3 which is what you want.

Hermelindahermeneutic

Why not select MIN(idColumn)-1? – Hypothermia

@ThiefMaster, because we have to get the NEXT available ID. The MIN will give the lowest available ID and +1 will give the next one. – Hermelindahermeneutic

Doesn't work if your id's start at (for example) 58 and what you want therefore is to know that ids 1 - 58 are free. So it should find 57 as the lowest available number. – Jervis

If ID column can be null, this can be very slow. What helps in that case is to add t1.ID IS NOT NULL to WHERE statement. – Ronnyronsard

@shamittomar, If id=1 is not in use, your query will not got it. But works! – Facelift

To find the maximum unused id (below the latest used ids), switch MIN with MAX and then all the + 1 to - 1. Grabs a skipped ID near the end of a list of IDs instead of near the beginning. – Glower

Answer 2

Do not reuse IDs. You usually have way enough available IDs so you don't have to care about fragmentation.

For example, if you re-use IDs, links from search engines might point to something completely unrelated from whatever is in the search index - showing a "not found" error is much better in such a case.

Hypothermia

thanks but this is not the case and i need to know how to get that min+unique+unused id – Backhanded

The question was "how", not "if" he should. – Ethnography

Answer 3

It's against the concept of surrogate keys to try to reuse IDs

The surrogate key is good because it idetifies the record itself, not some object in real life. If the record is gone, the ID is gone too.

Experienced DB developers are not afraid of running out of numbers because they know how many centuries it is needed to deplete, say, long integer numbers.

BTW, you may experience locking or violating uniqueness problems in a multithreaded environment with simultaneous transactions trying to find a gap in the ID sequence. The auto increment id generators provided by DB servers usually work outside the transactions scope and thus generate good surrogate keys.

Further reading: Surrogate keys

Brownell

each record have its own id, autoincrementing and everything..this uniqueId im creating is just because my client wants to be unique but also that dont exceed the total of products he added (its another field)..you guys make a lot of assumptions, but thanks for your input, maybe other people that fit what you said can benefit from this... – Backhanded

then i'd advise not to delete records, but mark them deleted in some field. when you'll need to fill the record with new data, you find the record minimum 'id' where deleted = true and update it. with proper transaction isolation settings the db server would take care of preventing 'dirty reads' so than another thread won't want to update the same record thinking it is still 'deleted' – Brownell

This is a good supplemental answer and adds weight and useful information to the overall SO. The first thing I thought when reading the question is why would you want to do that? The question did not specify the reasoning - and even now that it has been clarified above, it still does not make good sense. this uniqueId im creating is just because my client wants to be unique but also that dont exceed the total of products he added (its another field) - if you are using the ids to specify the total number of products, something is wrong – Ascender

This is not actually an answer to the question. An answer should assume the questioner has a valid reason for doing what they ask. This answer does the opposite. – Varioloid

Centuries of practice show that there is no valid reason for reusing an ID – Brownell

Answer 4

the query is like :

SELECT MIN(tableFoo.uniqueid + 1) AS nextID
FROM tableFoo
LEFT JOIN tableFoo tf1
       ON tableFoo.uniqueid + 1 = tf1.uniqueid
WHERE tf1.uniqueid IS NULL

Bittencourt

Answer 5

Note that the answers by shamittomar and Haim Evgi don't work if the lowest ID is free. To allow for the refilling the lowest ID, pre-check to see whether it is available:

SELECT TRUE FROM tablename WHERE ID = 1;

If this returns anything, then the ID of 1 is not free and you should use their answer. But if the ID of 1 is free, just use that.

Mozambique

Answer 6

In my personal opinion. Instead of removing the row from the auto increment it would be light years less expensive to have Boolean Column for "Removed" or "Deleted" and for extra security over right the row with blanks while you set the removed flag.

UPDATE table SET data=" ", removed = TRUE WHERE id = ##

(## is the actual id btw) Then you can

SELECT * FROM table WHERE removed = TRUE ORDER BY id ASC

This will make your Database perform better and save you dough on servers. Not to mention ensure no nasty errors occur.

Footstone

Answer 7

Given that your database is small enough, the correct answer is to not reuse your ids at all and just ensure its an auto incremented primary key. The table is a thousand records, so you can do this without any cost.

However, if you have a table of a few million records/longer id, you will find that the accepted answer wont finish in sensible time.

The accepted answer will give you the smallest of these values, correctly so, however, you are paying the price of not using an auto increment column, or if you have one, not using the auto increment column as the actual ID as it is intended (Like me, else I wouldn't be here). I'm at the mercy of a legacy application were the ID isn't the actual primary key is being used, and is randomly generated with a lolgorithm for no good reason, so I needed a means to replace that since upping the column range is now an extremely costly change.

Here, it is figuring out the entire join between the entirety of t1 and t2 before reporting what the min of those joins is. In essence, you only care about the first NULL t1 that is found, regardless of whether it actually is the smallest or not.

So you'd take the MIN out and add a LIMIT of 1 instead.

edit : Since its not a primary key, you will also need to check for not null, since a primary key field cant be null

SELECT t1.ID + 1 AS nextID
FROM tablename t1
   LEFT JOIN tablename t2
       ON t1.ID + 1 = t2.ID
WHERE t2.ID IS NULL
AND t1.ID IS NOT NULL
LIMIT 1

This will always give you an id that you can use, its just not guaranteed to always be the smallest one.

Ealdorman