Re-Generating ROWID

[Elham Peiravian]

HI everyone,

I needed to delete some rows in a table and then add some other rows to it. I noticed when I delete the rows, the ROWID does not autogenerate into a sequence and I have not been able to find a way to regenerate my ROWID values. Is there actually a way to achieve this?

[a.fu...@lqt.it]

On Tue, 14 May 2024 13:38:00 -0700 (PDT), Elham Peiravian wrote:
> HI everyone,
>
> I needed to delete some rows in a table and then add some other rows
> to it. I noticed when I delete the rows, the ROWID does not
> autogenerate into a sequence
>

Hi Elham,

just some preliminary clarifications:

1. the ROWID has nothing to do with SpatiaLite, it's something that
is handled directly by SQLite

2. for SQLite the ROWID is simply a unique number ensuring that
any row in a Table can be accessed very quickly in an unambiguous
way. Nothing ensures that the values ​​are in sequence, the only
relevant thing is that they are unique.

3. a ROWID cannot be confused with a Primary Key because it's not
stable and persistent. Operations like VACUUM can easily
reassing new values to ROWIDs.

4. it follows that it is not possible to use ROWIDs as robust
identifiers because they can change unexpectedly.

conclusion: basing your code on ROWID values ​​is a bad habit
that should always be avoided.
the right solution is instead to always explicitly declare a
Primary Key for each table in such a way so to rely on stable
row identifiers ​​that will never change.

two important things to consider:

a. for SQLite a column declared as INTEGER PRIMARY KEY
automatically coincides with the ROWID

b. the values ​​of a PK of this kind will be automatically
generated sequentially whenever that column is assigned
a NULL value.
but if the column is assigned an INTEGER value then this
will be preserved (provided that a duplicate is not created).
in short, using an INTEGER PRIMARY KEY you have the absolute
freedom to assign your ROWID values as you see fit, following
a progressive sequence or going at random.

> and I have not been able to find a way to regenerate my ROWID
> values. Is there actually a way to achieve this?
>

you are expecting something illogical and impossible.

think about it for a moment: to recover the ROWIDs of deleted
rows SQLite would have to do a complete scan of the Table for
each INSERT so to find out if there are any holes in the sequence.
a very heavy operation that would make each INSERT intolerably
slow.

however, if it is absolutely essential for you to have all the
PK/ROWID values ​​in a consecutive sequence without holes, a
possible solution exists.

just have your code keep track of each DELETE (perhaps recording
the deleted ROWIDs in a temporary table).
after which for each INSERT you'll recover the first ROWID value
available in the helper table; if this is empty you'll get a NULL,
and so the next value of the automatic sequence will be assigned.

obviously you'll have to immediately eliminate any value you recover
from the support table.

bye Sandro