Recover sequential auto increment after deleting some column

Asked

Viewed 359 times

0

I am using SQL server and the scenario is that I have a table with an auto increment id field, for example I have 3 field inserted, in case there are the ids 1, 2 and 3, if I delete the column with id = 3, and to continue an insertion, the ids following will be from 4, would have to use this that was deleted, ie have the data in sequence without skipping any id or not?

In this case it would also serve as an addition, if the id = 1, do a general update that makes others with sequential id from the first, occupying the place from which it was deleted?

sql database sql-server modeling

  • Unnecessary, unlikely some application use all possible indices INT to the point of needing to reuse the deleted keys and if it comes to occur an additional complexity imposed by a lack of planning.

1 answer

1


That doesn’t make sense, the ID should be used precisely to be a stable indicator of that line and so be able to use anywhere in the database, and even outside it in some cases, and be sure to always access the same data in a canonical way. If it doesn’t cause trouble, it’s because ID was false.

It is possible to make a mechanism to reuse IDare no longer used, but do not resequencialize since the change would require changing the ID everywhere in the database and ensure that there are no references to it outside of it.

Even if you can reuse it is necessary to do it with great care, I do not advise to naive people in programming, a lot can go wrong. That was kind of talking about How to resolve Sqlite auto increment gap?.

You can see more in It is necessary to create a primary key with auto increment? and Surrogate Key and Natural Key.

Browser other questions tagged sql database sql-server modeling

You are not signed in. Login or sign up in order to post.