Resolved Is it possible to retain auto increment even after deleting a tuple from a datagridview

Omer Butt

Active member
Joined
Sep 6, 2021
Messages
29
Programming Experience
Beginner
I want to ask if there is any possibility to retain the Auto Increment Primary Key Numbers after deleting a record. For Example there are 5 records save in a table if I delete a tuple ID 3 by clicking on a delete button from a datagridview it shows the ID sequence retained as 1,2,3,4 instead of 1,2,4,5.

Where it also should not disturb the database itself ..

If possible please explain how can we achieve this ?
 
Solution
What if it matter only for visual representation ?
It wouldn't. The ID is to uniquely identify the record and it can do that whether there are "holes" in the sequence or not. In fact, it will do it better if you leave the holes after deleting a record because then the subsequent values won't then identify a different record than they did before. If you're just trying to do this because you think it looks nicer then get over it. If you want to display records with a sequential record number with no holes then just assign a record number when you perform a query. That's what SSMS does, for instance. If you query a table then it will display record numbers specific to that query in the row headers and then the actual IDs in a...

JuggaloBrotha

Staff member
Joined
Apr 23, 2011
Messages
178
Location
Lansing, MI; USA
Programming Experience
10+
Why would this matter?
I mean if you're using Sql Server you could write a trigger to re-assign all of the ID's sequentially for the table's records after delete, but why would that matter?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,190
Location
Sydney, Australia
Programming Experience
10+
This is a silly request. If the ID value actually matters then you shouldn't be using an auto-incremented value in the first place. The whole point of such an ID is that it is generated without you having to care about it, so stop caring.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
4,190
Location
Sydney, Australia
Programming Experience
10+
What if it matter only for visual representation ?
It wouldn't. The ID is to uniquely identify the record and it can do that whether there are "holes" in the sequence or not. In fact, it will do it better if you leave the holes after deleting a record because then the subsequent values won't then identify a different record than they did before. If you're just trying to do this because you think it looks nicer then get over it. If you want to display records with a sequential record number with no holes then just assign a record number when you perform a query. That's what SSMS does, for instance. If you query a table then it will display record numbers specific to that query in the row headers and then the actual IDs in a column. Basically, what you're suggesting is a bad idea and you should not do it. You're not the first person to ask and everyone gets the same answer.
 
Solution

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,029
Location
Chesapeake, VA
Programming Experience
10+
What if it matter only for visual representation ?
As my database teacher in college said, "IDs are for the use of the system. If you want a human readable record identifier, use a separate column." Then he started a discussion of vanity plates for cars and whether the plate number should be used as a database row ID or not.
 
Top Bottom