[sqlite] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?

[Randy Eels]

Thanks a lot both Igor and Simon for the quick response! Your two answers
settle my doubts.

On Mon, Nov 16, 2015 at 3:48 PM, Simon Slavin  wrote:

> On 16 Nov 2015, at 12:24am, Randy Eels  wrote:
>
> > My main question is: is an 'INTEGER PRIMARY KEY' column guaranteed not to
> > change, except as a consequence of ordinary UPDATE statements? (And in
> > particular, not as a consequence of a 'vacuum' statement.)
>
> Values in the rowid column (which has a number of other names) can change
> only if that columns is not aliased by the table definition.  Declaring a
> named column as 'INTEGER PRIMARY KEY' counts as an alias.
>
> > I'd also thank further explanation as to why the 'vacuum' statement
> doesn't
> > change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared.
>
> If you declare your own column as being INTEGER PRIMARY KEY, the rowid
> will not change by anything SQLite does automatically.  This is because
> SQLite knows that you know what the column is called, so it thinks you
> might be relying on its values.
>
> The programmer can still change rowid by explicitly updating it, or any
> column aliased to it, using an UPDATE command.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Are columns declared as 'INTEGER PRIMARY KEY' guaranteed not to change except from UPDATE statements?

[Randy Eels]

Using SQLite 3.8.7.1.

Here

and elsewhere

is
mentioned that the official SQLite documentation includes the line

Rowids can change at any time and without notice. If you need to depend on
> your rowid, make it an INTEGER PRIMARY KEY, then it is guaranteed not to
> change


However, I haven't been able to find that in the official documentation. I
wonder whether such an assertion is true.

Any column declared as 'INTEGER PRIMARY KEY' is said to be an alias for the
internal 'rowid' column, and this

proves
that 'rowid' can change after a 'vacuum' statement is issued. Merely
replaying the steps in the linked post, but declaring an additional column
as 'INTEGER PRIMARY KEY' shows that even after 'vacuum' both the rowid and
the declared primary key remain unchanged.

My main question is: is an 'INTEGER PRIMARY KEY' column guaranteed not to
change, except as a consequence of ordinary UPDATE statements? (And in
particular, not as a consequence of a 'vacuum' statement.)

I'd also thank further explanation as to why the 'vacuum' statement doesn't
change 'rowid's when an explicit 'INTEGER PRIMARY KEY' is declared.

Thanks in advance.