0

I have deleted a row in my database with the value of 8. the problem is when I add the next row the auto number starts with 9 not 8. it stills consider the maxium existing value equal to the row whivh was already deleted. how can I fix this?

Lily
  • 1
  • MySQL <> SQL Server. Which platform are you actually using? The responses will be completely different for each. – squillman Aug 04 '22 at 14:34
  • 1
    Even if you correct this one, you can still expect gaps in an identity. https://stackoverflow.com/questions/14642013/why-are-there-gaps-in-my-identity-column-values#:~:text=Consecutive%20values%20after%20server%20restart,the%20identity%20value%20upon%20insert. – John Cappelletti Aug 04 '22 at 14:35
  • Why ***exactly*** do you need it? If you need row numbering and/or dense ranking, it can be achieved by other means. – PM 77-1 Aug 04 '22 at 14:36
  • 1
    What it sounds like you have is a situation whereby the ID value actually matters (i.e. it's not an arbitrary identifier). A common use case for this is storing enums from the application in a table. If that's the case (and I suspect that it is given that you have so few rows in the table), I'd posit that you shouldn't be using an identity column but rather if/when you need to insert rows into the table that you specify the value for the ID column explicitly. – Ben Thul Aug 04 '22 at 16:20
  • Either that, or disallow deletion. Mark the record as deleted. And either leave it there or reuse it next time. It all depends on what the intention is. We had this situation in a Billing module in Hong Kong. The tax auditors insisted that the bill numbers needed to be sequential. So, we prevented deletion and just empties the bill. – Rohit Gupta Aug 05 '22 at 05:10

1 Answers1

1

You can use:

DBCC CHECKIDENT ('tableName', RESEED, 7);
GO
s241k
  • 39
  • 6