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

.net c# entity-framework-core sql sql-delete

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:

enter image description here

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

1
1
3/13/2020 7:03:21 PM

Accepted Answer

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.

0
3/13/2020 12:57:19 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow