how to automatically set new id value as id of already deleted row

Question

I have a table with 50 rows, id column as primary key, autoincrement.

At the beginning id values where from 1 - 50;

After deleting a row, for example id=1, and inserting a new row, this will have id=51.

How can I set the new row to take first empty place, it this case id=1 automatically, instead of 51;

Also if id=5 is deleted, the next inserted row should have id=5 an so on.

don't delete the row in DB just update that with new user value — Bibhudatta Sahoo, Jul 28 '17 at 07:46
Don't really see the point of this either, but you could `SELECT` the first empty id before doing your `INSERT` query. — roberto06, Jul 28 '17 at 07:47
Possible duplicate of [SQL: find missing IDs in a table](https://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table) — Salketer, Jul 28 '17 at 07:50
write a `trigger` for specfic table to store the deleted id in `temptable` while `insert` check any id is exists in `temptable` if so use the id and delete tat id from the temptable or else leave it `null` `auto increment` will do the rest — Arun pandian M, Jul 28 '17 at 07:51
If that deleted `ID` will be linked with some other table. Then, you will get unwanted results. @Bonaca I recommend you not to use. — Nana Partykar, Jul 28 '17 at 07:51
@axiac, the benefit is in automatically creating a short password for each row, which is `id` value multiplied with 9, for example. If `id` is long, the password is too long. — qadenza, Jul 28 '17 at 07:57
There is no benefit. You're using the `auto_increment` field wrong. The "gaps" occur for a reason. When you try to "fill" the gaps, you open a pandora's box. But hey, why should you listen to some programmers on internet telling you this is bad, they probably have no clue, right? What's funny is that you're the one asking the question.. but you know better. — Mjh, Jul 28 '17 at 08:00
@Mjh, my question is not is it bad or not. The question is how to do something. — qadenza, Jul 28 '17 at 08:02
Reading your comment on why you need this - it's absolutely useless. Questions such as yours give other visitors bad ideas. It matters whether what you want to do is bad or not because we need to *responsibly* handle knowledge. You're using the `auto_increment` field for something that's not it's purpose. You have some sort of logic with passwords that depends on value of `auto_increment` field. You've done two very stupid, very dangerous things - and you don't heed the warning, which is fine. I'm not answering, I'm commenting, hoping it will warn others not to walk across the burning bridge. — Mjh, Jul 28 '17 at 08:05
If password is the only reason to reuse the IDs then I think you are trying to solve the wrong problem. I can imagine a handful of other ways to generate a short password. I hope it is a temporary password. — axiac, Jul 28 '17 at 08:06
Reusing the IDs is wrong at its heart. An ID must **uniquely identify** an object. Is the new object the same thing as the deleted object? I guess not. They are different entities, apart from the reused ID, they have different identities, they cannot be replaced one for another. Reusing the IDs has undesired effects in the future, in ways you cannot predict now. — axiac, Jul 28 '17 at 08:08
@axiac, no, the new object cannot be the same as deleted object, because deleted object IS DELETED, it doesn't exist. — qadenza, Jul 28 '17 at 08:12
I'm not talking about the data you store in the database. I'm talking about the object it represents in your system. The deleted object played a role in your system. It was probably linked to other entities, it possibly left traces in log files, appeared in generated URLs, object collections etc. It was identified by its unique ID in all these places (or otherwise the ID column is useless in the table). If you now assign the old ID to a different object, many things could go wrong. It's identity theft, you know... — axiac, Jul 28 '17 at 08:36

score 2 · Answer 1 · answered Jul 28 '17 at 07:59

With autoincrement columns you can not change the keys checks per row.
You could disable key checks and the truncate the table which will reassign new keys(numbers) to each row, but that means previous keys will move to other rows depending on how many deleted rows and where they were in the sequence.

If you really need to do something like what you are doing, you could alternatively create a faux-indexed "auto" incremented column by your script. Then you would be able to manage that column in any manner that you chose to.

score 1 · Accepted Answer · answered Jul 28 '17 at 07:58

You can use MySQL's query variables to track the previous id and calculate the difference in the query, e,g.:

SELECT id, id - @previous AS difference, @previous := id
FROM test, (SELECT @previous := 0) a
ORDER BY id;

This would give you all the ids with difference. You can then wrap this into another query and SELECT the rows with difference > 1 to get the available id, e.g.:

SELECT (id - difference + 1) AS available_id
FROM (
 SELECT id, id - @previous AS difference, @previous := id
 FROM test, (SELECT @previous := 0) a
 ORDER BY id) b
WHERE b.difference > 1
LIMIT 1;

Here's the SQL Fiddle.

how to automatically set new id value as id of already deleted row

2 Answers2