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.
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.