4

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?

showdev
  • 28,454
  • 37
  • 55
  • 73
Alessandro Minneci
  • 281
  • 5
  • 24
  • 5
    Why is this a problem? The primary key is just a unique identifier, that's all – OTTA Jun 22 '16 at 10:43
  • You need to reset 'Identity Seed' value for ID column in database programmatically. – D Mayuri Jun 22 '16 at 11:34
  • You *can* re-use the 5 (but only the 5) if you [delete it and then add something new](https://stackoverflow.com/a/18692183/5049813). (I'm adding this comment because I didn't think that what I just described was possible and it led to a bug on a production site because I used the question I'm commenting on as evidence that what I just said was impossible to do. Hopefully this will save another programmer from going through the same.) – Pro Q Sep 02 '20 at 21:40

4 Answers4

3

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

Community
  • 1
  • 1
mark_h
  • 5,233
  • 4
  • 36
  • 52
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.

Whencesoever
  • 2,218
  • 15
  • 26
1

Lets say i have 5 Records with the ID's 1-5. If i create a new record after deleting these 5 ID's, the ID of the new one is 6, but it should be one.

It seems your id column is Identity.You need to use below command after deleting 5 rows everytime to start your value again from 1

DBCC CHECKIDENT (yourtable, RESEED, 1);

this wont work unless you truncate table,since you have a primary key on Id,so you will need work on your primary key strategy

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
1

because SQL server does not track which unique identifier deleted , So you have only one option to reset that

DBCC CHECKIDENT (yourtable, RESEED, 0);

This is not you looking for but SQL server does not track which are deleted.

Ashokk
  • 11
  • 1