mysql auto increment primary key id's

Question

How do you look for a delete ID number that has been used, but deleted as is no longer used, before you just append on top of the last ID number that has been added

For example:

  ID      Name

  1       Orange
  5       Apple

I have auto increment on, I want to add Banana in to number 2, but auto increment is adding it as ID 6

I'm doing sql in myphpadmin/xampp for a website

Any help would be much appreciated, thanks :)

Answer 1

Thats just not how auto-increment works.

I'm almost positive you'll have to find missing ids and assign them yourself.

You may want to re-tag your question to include the database your using. I'm assuming MySql?

Answer 2

You would have to write your own code to identify the lowest unused identifier and assign it manually in your INSERT query. Your AUTO_INCREMENT column is working properly, it will not reuse deleted identifiers.

Answer 3

I agree... It's just how auto-inc works.

If you are running MySQL, I think it has something built-in which does this for you, but I could be wrong since I can't find it in the documentation.

You can also write a script yourself. It'll be a pretty intense script on both the web server and the database server so it shouldn't be ran too often.

Auto-inc has worked the same for years--since I can remember, anyway. So, it's obviously doing something right. Unless you have a particular reason for not wanting gaps in your primary keys (and even then, you should seriously reconsider what you're using the field for), just let it be. If the field is int 10 and unsigned, you will have plenty of wiggle room...

-- Logan

Answer 4

Banana having an ID of 6 is correct — this is working as it was intended. All auto-increment does is allow you to insert new rows into the table without knowing the previous ID. Basically allows you to insert data without worrying about overwriting the ID, and you can be lazy.

The way auto increment works is that it has a counter that goes up for every row inserted. When you delete rows the counter doesn't change.

I think what you are expecting is that the database would automatically move down rows to fill in the missing IDs. While this might be true in a linked list data structure, it is not in this case.

The only way you can fill in those gaps is to either manually do it, or write a script that would either fill the gaps or rearrange the table or ids. There is really no need for this though, because when you retrieve the rows for markup use, you can use an ordered list or a trivial loop to handle this, and the gaps would simply be irrelevant.

Answer 5

I think that generally the auto-increment feature is used to create a unique key on tables and that re-using the unique key isn't the normal use of the key, because deleting a record and then inserting one doesn't necessarily mean that the record being inserted is the same one that was deleted. i.e. the key wouldn't actually be "unique" in the intuitive sense because two rows have shared that key, even though technically there would be no unique key constraints that are broken.

Please note that with your own implementation you have to worry about concurrency issues. i.e. two inserts are happening at once so...

1) user A wants to insert so searches for the lowest available ID
2) user B wants to insert so searches for the lowest available ID
3) user A finds the lowest available ID of 2
4) user B finds the lowest available ID of 2
5) user A inserts a row and uses ID 2
6) user B inserts a row and tries to use ID 2 and either succeeds (which makes the key no longer unique) or fails because ID 2 has already been used..

What's the intended use of this?

Answer 6

Ok, I have an answer. But if you use it, I'm going to come out there and beat you up! This subquery will find the lowest hole in the IDs. Just replace the word table with your table name.

INSERT INTO
    table
SET
    id = (
        SELECT
            MIN(a.id + 1)
        FROM
            table a
            LEFT JOIN table b
                ON a.id + 1 = b.id
        WHERE
            b.id IS NULL
    ),
    name = 'next'