-1

When I save something in my database and i delete it from the Table Data, and when i try to re-save something back into it, The Id doesn't count from where it left from. I don't know much about databases, when i try to research about it, i don't know what people mean by "RESEED", i don't know how to work the code.

I have tried "RESEEDING" using a specific code i found, but it doesn't seem to work.

I want to fix this issue using the simplest method for BEGINNERS like me, please help me with the issue.

ADyson
  • 57,178
  • 14
  • 51
  • 63
Scretqer
  • 25
  • 7
  • This has nothing with `c#`, put SQL vendor tag instead – Renat Aug 02 '19 at 08:21
  • 1
    IDs are not intended to be re-used, if that's what you're asking. They must uniquely identify a specific row forever, even after it's been deleted. You could re-start the count, the DB engine will likely allow this, but it's not usually a good idea from a logical point of view. Think about if you have records in other tables (e.g. audit history) relating to user ID 3, and then later you re-use the ID, so User ID 3 comes to represent a different person...all your audit trails etc are now false. I say this because I question why you need this feature. – ADyson Aug 02 '19 at 08:23
  • Possible duplicate of [Reset identity seed after deleting records in SQL Server](https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server) – prinkpan Aug 02 '19 at 08:24
  • so basically, I'm making car service program, where the manager registers clients, which saves all their data such as name, address, email, phone to the database with ID to represent each one. Basically when i want to add a feature for the manager to delete the client, the ID will it still stay there because when i remove them through the Table Data, and i register a new client the ID continues from the one i removed manually. – Scretqer Aug 02 '19 at 08:27
  • 1
    "the ID continues from the one i removed"...that's strange, it shouldn't happen like that. By any chance are you creating the IDs manually, instead of using an identity column with auto-increment? – ADyson Aug 02 '19 at 08:29
  • As @ADyson explained well, your shouldn't use the ID as a way to identify a record for your customers. They shouldn't never see it and never rely on it to search or update a record. They use something else like name, surname, fiscalcode, social security number, whatever is used in your country to always identify a person. Your IDs belongs to you and to your database logic. Never re-use an ID. – Steve Aug 02 '19 at 08:31
  • No, i am using the identity column with the property Identity Specification set to true. – Scretqer Aug 02 '19 at 08:31
  • @Steve do you mean setting the Primary Key to something other than the Id? – Scretqer Aug 02 '19 at 08:35
  • @Scretqer what you're describing makes no sense, then. If you have that identify specification set correctly, then if you delete the row with ID 3, and ID 3 was the last row in the table, then when you insert a new row it will have ID 4. 3 cannot (and should not) get re-used by the identity generator. If your table is really configured the way you say, then you should not have any problems. – ADyson Aug 02 '19 at 08:36
  • @ADyson look at this image [link](https://ibb.co/Wxk4nHJ) that is what my Data Table looks like after i delete something that i have saved, the Id doesn't increment correctly. – Scretqer Aug 02 '19 at 08:45
  • 1
    Your screenshot doesn't really explain the problem by itself. What should I be looking for in that picture? All I see are two rows. Did you delete something? Did you add something afterwards? Give me a specific real example. "not incrementing properly" isn't enough info. Please also use SSMS to generate a CREATE TABLE statement for this table and post it into your question. Then we can see exactly how the table is set up. – ADyson Aug 02 '19 at 08:47
  • 1
    No the ID will be always your PrimaryKey, eventually you could add Indexes to other columns in that table to get a faster search time when your user ask for a record data using another UNIQUE identifier (like the FiscalCode, the SSN etc) – Steve Aug 02 '19 at 08:53
  • @ADyson [image](https://ibb.co/LJYt6nc) of my table generator, the previous image i sent shows the data i saved after i deleted the data i had saved with Id's 1,2 and 3. Now after i deleted these data by selecting the data from the table in the first image and tried to add new data using my program the Id continues from 3 so 4, 5 etc My question is why does it do that, i want it to start from 1 again not 4, the Id is still saved or something, i don't know how to fix this issue. – Scretqer Aug 02 '19 at 08:56
  • 2
    It's **not** an issue. That's how it's supposed to behave. Starting from 1 again is not a good idea, unless you are just deleting your whole database to start over with testing. Did you read and understand my first comment above? It explains why what you are asking for is not logical in a database – ADyson Aug 02 '19 at 08:58
  • @ADyson So the data that i save will always stay there even if i want to delete it from the data base. It just continues from what the Id was previously???. How would i reset the Id if i wanted to? – Scretqer Aug 02 '19 at 09:07
  • and @Steve i understand that the user should not see or interact with it, i am only using Id as a reference for myself which won't be visible to the user whatsoever. – Scretqer Aug 02 '19 at 09:08
  • 1
    Then what is the point in changing that ID? You will get only more work to do when you add new tables that need to use that ID as a reference to the data in the table Clients. Think about it. You have clients with ID 1,2,3,4,5 then you delete the ID 3, what do you do now? Change the ID 4 and 5 to close the gap? It makes no sense and what if another table uses those ids (4,5) to link its data to the Clients table? If you really want to jump into this mess you have the link above by @PriyankPanchal – Steve Aug 02 '19 at 09:17
  • @Scretqer No, the data gets deleted. However the ID does not get re-used. Those two things are not the same. If you _do_ need to reset the IDs (e.g. for testing) then see the link above in the comments, mentioned as "possible duplicate" - the answer there explains how to reset an identity column in SQL Server. But you should not use this once your application goes live, it's only for helping with testing – ADyson Aug 02 '19 at 09:18
  • holy flip, okay i understand now, sorry for all this fuss, I wasn't sure why the Id did that but now its more clear. Thanks Guys :) – Scretqer Aug 02 '19 at 09:23

2 Answers2

1

As people have commented, identity is not meant to be a gap-less enumerated sequence and there is nothing wrong with having gaps in it.

That said, I see no reason to use a surrogate key like an identity enumeration in this case. A surrogate won’t protect your data from duplicates by inserting the same customer more than once, and can’t be used to identify customers as it is not an attribute of a real customer. That is the only purpose of a key.

Find the business key, which is what the business uses to uniquely identify customers, and that should be your primary key. This could be email, phone, or whatever other attributes the business uses. This will guarantee that you have no duplicates, and make your queries simpler and faster as you will need to perform far less joins. With a meaningless surrogate key which abstracts your customers real attributes, you will be forced to join back to this table in every query that deals with customers.

HTH

SQLRaptor
  • 671
  • 4
  • 14
0

Reset the Identity value using DBCC CHECKIDENT (Table_Name, RESEED, 1)

example :

suppose the Table(ABC) has 2 columns "Id" and "Name"

2nd row's Id became 3. So, to reset it back to 2 follow the following steps:

Delete from  ABC where Id = 3
GO 
DBCC CHECKIDENT ('ABC', RESEED, 1) 
GO
Insert into ABC values ('RAHUL')
GO

This will reset the identity column to 2

Anand Nikhil Bharti
  • 1
  • 1