How can I delete a database record and reuse the deleted primary key?

Question

I've created a database in VS 2016. I also wrote a WebApplication (ASP.Net/C#/Entity Framework) where I can enter different values in different textboxes. These get saved in my database. Every record receives an ID (Primary Key).

Lets say I have 5 records with the IDs 1-5. If I create a new record after deleting these 5 IDs, the ID of the new one is 6, but I'd like it to reuse the ID of 1.

How can I achieve this?

Answer 1

I presume you are using auto-increment to assign a new key value to your Id column. You are assuming that since there are no records then the next free value would be 1, but it just keeps incrementing from the previous highest value.

Its a bad idea, but if you really wished to recycle your key values then you could switch off auto-increment and manually manage your key values yourself, but this is error prone and difficult.

Do you really need to do this? Int and BigInt can hold very large numbers, are you likely to ever run out of key values so that recycling might be required?

If you just want to reset your auto-increment back to 1 I suggest you look at this post

Answer 2

It's not a problem. Your database has Primery Key with Identity(1,1). Every new record will have ID greater than the last one added.

If You are deleting all the rows You can use

TRUNCATE TABLE tablename

. Otherwise You might think of turning off autoincrementation and providing number for ID with function looking for smalles free Id in Your table.