How do I get first unused ID in the table?

Question

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.