Winforms database Id primary key keeps counting

Question

So I am making a program where i can make, save and delete items from a database, but i want the id's of existing items to follow up on eachother. Example, I have these items:

|  Id |  Item name  |
----------------------
|  1  |  item 1
|  2  |  item 2
|  3  |  item 3

then when i delete item 2 i want it to be:

|  Id |  Item name  |
----------------------
|  1  |  item 1
|  2  |  item 3

and not:

| Id  |  Item name  |
----------------------
|  1  |  item 1
|  3  |  item 3

This is my form:

Why do you think you want this? There are many problems that this would cause, but what problem would it solve? — David, May 21 '20 at 15:13
A identity type ids such as that are only meant to be unique - not sequential. When you have related data in your relational database, re-sequencing is a major problem. They also are not meant to have any other intrinsic value. — Ňɏssa Pøngjǣrdenlarp, May 21 '20 at 15:13
i use the id as a reference at what position they are at in the list. Can i do this on another way? — Daan Van de Voorde, May 21 '20 at 15:14
Typically you don't want to do this but you'll have to reset the identity seed. If you're using SQL Server, see https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server — M W, May 21 '20 at 15:14
@DaanVandeVoorde: "Position" is an entirely relative term. Sort order is not guaranteed without an explicit `ORDER BY` clause, and unless you order by the `Id` then it'll be out of sequence anyway. It sounds like you're trying to use this value for a purpose for which it wasn't intended. What do you need this sequence for? — David, May 21 '20 at 15:15
I made a program where i can insert and delete items from a database and these items are displayed in a ListView, i Need to be able to check the id of the item in there. I will add a photo of my form in the question — Daan Van de Voorde, May 21 '20 at 15:19
@DaanVandeVoorde: So... You need to display the records on your form? What's stopping you from doing that now? — David, May 21 '20 at 15:20
the numbers don't start from 1 and if i delete one the number stays the same — Daan Van de Voorde, May 21 '20 at 15:22
@DaanVandeVoorde: That is expected, documented, and correct behavior for an ID. How does that prevent you from displaying the data? What is the *actual problem* that you're trying to solve? — David, May 21 '20 at 15:22
the first column should start from 1 and if i delete an item its number should change so it follows up to the number above it. — Daan Van de Voorde, May 21 '20 at 15:25
@DaanVandeVoorde: What you're describing is custom logic that *only you* want. To implement that custom logic, you're going to have to write code. In your code where you delete a record, follow it up by updating the rest of the records with your new custom information. The database isn't going to implement your custom, entirely non-standard, requirements itself; you have to do that. — David, May 21 '20 at 15:26

score 0 · Answer 1 · answered May 21 '20 at 15:15

but i want the id's of existing items to follow up on eachother.

So do not use Identity. Simple. Identity does not follow the rules of waht you want, but the rules of that MS has programmed and it says "once given it is not reused". Period.

RTFM will tell you there is no quarantee in there of reuse. You have to sit down and program a way to get similar id - with all the side effects that you may get business wise if you ever expose the identity. And the performance impact.

To comment: i use the id as a reference at what position they are at in the list.

That is an abuse. If you want an order, I would have ANOTHER field for that, so you can reorder them. You need an order by anyway (unless you want to follow another worst practice of implying that the pk order is maintained - which will work absolutely fine, until one day your orders change and coming here then you are edcuated on that there is no guaranteed order unless you ask for one).

score 0 · Answer 2 · answered May 22 '20 at 00:19

The numerical values in the primary key is not normally used that way. If you want a numerical order of the records, you can make use of this query when you retrieve the data

  SELECT 
  ROW_NUMBER() OVER(ORDER BY Id ASC) AS OrderNum,ItemName
  FROM yourTable

Then let the primary key auto increment itself and do its job in being unique.

Winforms database Id primary key keeps counting

2 Answers2