How do I get first unused ID in the table?

Question

Dear all,

I have to write a query wherein i need to allocate a ID (unique key) for a particular record which is not being used / is not being generated / does not exist in database.

In short, I need to generate an id for a particular record and show it on print screen.

E. g.:

ID  Name

1   abc
2   def
5   ghi

So, the thing is that it should return ID=3 as the next immediate which is not being generated yet, and after this generation of the id, I will store this data back to database table.

It's not an HW: I am doing a project, and I have a requirement where I need to write this query, so I need some help to achieve this.

So please guide me how to make this query, or how to achieve this.

Thanks.

I am not able to add comments,, so thats why i am writing my comments here.. I am using MySQL as the database..

My steps would be like this:-

1) Retrieve the id from the database table which is not being used..

2) As their are no. of users (website based project), so i want no concurrency to happen,, so if one ID is generated to one user, then it should lock the database, until the same user recieves the id and store the record for that id.. After that, the other user can retrieve the ID whichever is not existing.. (Major requirement)..

How can i achive all these things in MySQL,, Also i suppose Quassnoi's answer will be worth,, but its not working in MySQL.. so plz explain the bit about the query as it is new to me.. and will this query work in MySQL..

Answer 1

I named your table unused.

SELECT  id
FROM    (
        SELECT  1 AS id
        ) q1
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    unused
        WHERE   id = 1
        )
UNION ALL
SELECT  *
FROM    (
        SELECT  id + 1
        FROM    unused t
        WHERE   NOT EXISTS
                (
                SELECT  1
                FROM    unused ti
                WHERE   ti.id = t.id + 1
                )
        ORDER BY
                id
        LIMIT 1
        ) q2
ORDER BY
        id
LIMIT 1

This query consists of two parts.

The first part:

SELECT  *
FROM    (
        SELECT  1 AS id
        ) q
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    unused
        WHERE   id = 1
        )

selects a 1 is there is no entry in the table with this id.

The second part:

SELECT  *
FROM    (
        SELECT  id + 1
        FROM    unused t
        WHERE   NOT EXISTS
                (
                SELECT  1
                FROM    unused ti
                WHERE   ti.id = t.id + 1
                )
        ORDER BY
                id
        LIMIT 1
        ) q2

selects a first id in the table for which there is no next id.

The resulting query selects the least of these two values.

Answer 2

Depends on what you mean by "next id" and how it's generated.

If you're using a sequence or identity in the database to generate the id, it's possible that the "next id" is not 3 or 4 but 6 in the case you've presented. You have no way of knowing whether or not there were values with id of 3 or 4 that were subsequently deleted. Sequences and identities don't necessarily try to reclaim gaps; once they're gone you don't reuse them.

So the right thing to do is to create a sequence or identity column in your database that's automatically incremented when you do an INSERT, then SELECT the generated value.

Answer 3

are you allowed to have a utility table? if so i would create a table like so:

CREATE TABLE number_helper (
    n INT NOT NULL
   ,PRIMARY KEY(n)
);

Fill it with all positive 32 bit integers (assuming the id you need to generate is a positive 32 bit integer)

Then you can select like so:

SELECT MIN(h.n) as nextID
FROM my_table t
LEFT JOIN number_helper h ON h.n = t.ID
WHERE t.ID IS NULL

Haven't actually tested this but it should work.