How to keep ID from increasing and use first available unused ID instead?

Question

When I delete an Item from my table I want that when adding next Item in that table that item should use the first available unused ID. How can i achieve this? When I deleted all of the Items and created new ones this happened:

In this case it would be much better that Item Id 21 was 1.

Answer 1

I would recommend against modifying (what looks like) a primary key column. As an example of side effects: if other entities are referencing the primary column, this will fail, or break the relations. Also, you potentially need to renumber the whole table for every delete that is executed.

If you want a dynamic auto-incremented number, you can use row_number() in a view:

create view myview as
select
    row_number() over(order by item_id) item_id,
    title,
    description
from mytable 

You can then query the view instead of the table, which gives you an always up-to-date increment number.