F
F
fukenrice2_02021-10-28 00:14:45
SQL
fukenrice2_0, 2021-10-28 00:14:45

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

3 answer(s)
S
Sergey Gornostaev, 2021-10-28
@sergey-gornostaev

First, indexing is not what you described. Secondly, the meaning of identifiers is precisely that they never change.

A
Akina, 2021-10-28
@Akina

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.

R
rPman, 2021-10-28
@rPman

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
...

by the way, instead of half numbers, you can first shift all the numbers more than necessary (for example, insert the 3rd one, add the condition where num> 3 and then insert.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question