1

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:

enter image description here

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

GMB
  • 216,147
  • 25
  • 84
  • 135
andersbs
  • 187
  • 1
  • 11
  • It doesn't sound like you're thinking about the full ramifications of what you're asking. If any ID were deleted at any time, that would mean you would need to then update every single record in that table and all related tables for all rows past the ID of the deleted ID. – Kritner Mar 13 '20 at 12:58
  • So I should just leave it like this? I – andersbs Mar 13 '20 at 13:01
  • 1
    yes, just leave it. It is correct and default-ish behaviour. – nilsK Mar 13 '20 at 14:24

2 Answers2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
0

You mean you want to rearrange the Autoincerment ID back to 1? I believe this will solve it Reorder / reset auto increment primary key

Shinjid
  • 35
  • 4