Delete an item (row) from table but keep the unused ID number for a future insertion
I have a database with an "inventory" table which contains products from a shop. Each product is identified using an ID number set in the "ID" column of the table. I want to be able to delete a product from the table, but keeping the deleted product's id number for future product insertions into the database. As a demonstration I inserted 4 items and named all of them "test"
And just as an example I named the "deleted" product as "vacio" (empty in spanish) to show the one that i deleted.
Now, if want to add another product in the future, the id number 2 is unused and I want to add the product with that id number instead of 4 (following the given example).
The DELETE query is no good since it erases the id number as well so its a no go. I thought about checking for the first row of the table that contains the value "vacio" and using the UPDATE query in all fields except id but this doesnt feel "classy" and is not very efficient as It should have to update values a lot of times. Is there some nice way of doing this?
I would not actually recommend reusing old identifiers. For one, this prevents you from using the auto_increment feature, which mean that you need to manually handle the column for each and every insertion: this adds complexity and is not efficient. Also, it might cause integrity issues in your database if you have other tables referencing the product id.
But if you really want to go that way: I would go for the deletion option. If there are foreign keys referencing the column, make sure that they have the on delete cascade option enabled so data is properly purged from dependent tables when a product is dropped.
Then, you can fill the first available gap the next time your create a new product with the following query:
insert into products(id, categoria, producto)
select min(id) + 1, 'my new category', 'my new product'
from products p
where not exists (select 1 from products p1 where p1.id = p.id + 1)