Is `rowid INTEGER PRIMARY KEY` an alias to the actual rowid?
(1) By trevyn on 2021-02-28 05:16:05 [link] [source]
https://sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key says (emphasis added):
If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.
With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.
The exception mentioned above is that if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key.
These excerpts seem like they may technically conflict — what is the specified behavior for
CREATE TABLE t(rowid INTEGER PRIMARY KEY)? Is this
rowid column an alias for the real rowid? If so, the first excerpt may be slightly too bold to say that it "cannot be used to retrieve the integer rowid value".
In particular, I'm interested in
sqlite3_blob_open(), where I need access to the true rowid, but I would also like to explicitly specify it in my schema.
(2) By Keith Medcalf (kmedcalf) on 2021-02-28 05:45:26 in reply to 1 [link] [source]
If a table contains a user defined column named "rowid", "oid" or "rowid", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.
That means that if there is a column explicitly named
_rowid_ then usages of those names refer to the declared column and not the rowid of the table (assuming that it is a rowid table).
So if you declare a table thusly:
create table x ( myrowid integer primary key, rowid integer, oid integer, _rowid_ integer );
then references to
_rowid_ refer to the columns by those names and references to
myrowid are references to the table rowid.
If the table is declared thusly:
create table x ( myrowid integer primary key, rowid integer, oid integer );
then references to
oid refer to the columns by those names and references to
_rowid_ refer to the rowid of the table (assuming that the table is a rowid table).
Mutatis mutandis other combinations.
That is to say more clearly that references to the magical names
_rowid_ refer to the column in the table by that name, or if and only if that column name does not exist in the table, then to the rowid of the table if the table is a rowid table, otherwise raise an error that the name does not exist.
This does not preclude giving an explicit name to the rowid of the table after which the rowid may be referred to by the name so explicitly given to the rowid, or such magical names
_rowid_ as are not already being used for another purpose.
Furthermore, if no explicit name is given to the rowid of a rowid table then the rowid is subject to change at any time for any reason whatsoever without notice to the user and may not be used as either the source or destination of a foreign key constraint.
(3) By trevyn on 2021-02-28 06:12:14 in reply to 2 [link] [source]
Thank you! So considering:
create table x ( rowid integer primary key, );
This is not a "user defined column" named
rowid, but rather a declaration that simply gives an explicit name to the table's internal rowid (solely because it is declared as
INTEGER PRIMARY KEY)?
(4) By Keith Medcalf (kmedcalf) on 2021-02-28 08:19:03 in reply to 3 [link] [source]
It is an explicit column called
rowid that gives an explicit name to the table's rowid. The magic names
_rowid_ will also refer to the same column since those names are not explicitly used in the table declaration and the table is a rowid table.
rowid will also be stable (will be preserved and will not change except by your changing it like any other explicit column) because it is explicitly named and thus can be used as the source/target of a referential constraint.
(5) By Simon Slavin (slavin) on 2021-02-28 11:32:55 in reply to 1 [source]
Worth adding to Keith's excellent answers, the behaviour of things like
If you have no reference to rowid when you create the table, SQLite concludes that rowid is for internal use only, and commands like
VACUUM might change values in it.
On the other hand, if your
CREATE TABLE statement supplies an alias (as used in this thread) for rowid, or explicitly declares a column called rowid, then
VACUUM will not change values in that column. Because you might have stored them elsewhere for future use.