Answer the question
In order to leave comments, you need to log in
How to reindex in a SQLite table?
The table has an id column which is the primary key. Is it possible to reindex them when deleting a random record from the table so that the indices continue to go in a row and there are no spaces like id=1,id=2,<this record was deleted>, id=4 back). Thanks in advance for your replies
Answer the question
In order to leave comments, you need to log in
First, indexing is not what you described. Secondly, the meaning of identifiers is precisely that they never change.
The question is the result of an absolute misunderstanding of the essence of the primary index.
A primary index is defined on a table in order to uniquely identify a record for the lifetime of the table.
Pay special attention - during the lifetime of the table . Not records! That is, the fact that the record is deleted does not affect anything - the missing value of the primary index identifies the fact that the record existed, but was deleted.
If you really need continuous numbering of records, then to implement this function, you should create an additional field in the table, and already in it you can deal with numbering, renumbering and compression. Or (which is more correct), calculate such a value directly in the request, at the moment when this value was required. And taking into account the fact that in the overwhelming majority of cases no function is assigned to this value, except for the convenience of viewing, it can not be considered at all. As a rule, the means of displaying a set of records on the client have a built-in function for numbering records in order - that's it.
In the answers, everyone said correctly, but they didn’t give the actual solution, here it is:
create a num field in which the serial number of the record will be placed, when requesting records, do group by num
with some order modifications (deleting or, for example, inserting into the middle of the record order) do number with a half (for example, insert an entry between 3 and 4, then let num be 3.5) and update the order
update table set
num = ROW_NUMBER () OVER ( ORDER BY num ) RowNum
...
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question