> Any column declared as 'INTEGER PRIMARY KEY' is said to be an alias for > the internal 'rowid' column, and this > <http://www.sqlabs.com/blog/2010/12/sqlite-and-unique-rowid-something-you- > really-need-to-know/> > 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. The implicit rowid is merely exposing the actual primary key (row number in the btree storing the table). Because it is not declared in the table definition, it does not contain a value that is relevant. The fact that there is a rowid is merely a side-effect of the fact that anytime you store data, you have to have a rowid. If you explicitly declare an INTEGER PRIMARY KEY, then this data is used for the rowid (since the table has to have a UNIQUE INTEGER rowid, the declaration merely assigns "meaning" to the rowid which needs to exist anyway. In other words, when you create table x (y text); the only "data" in the table is the text field y. That it has to have a rowid is immaterial. Contrast to create table x ( x integer primary key, y text); where you have now "related" the value of x to a value of y. Think of it like a spreadsheet. In the first case, you have not related the value "y" to the row it is on. In the second case you have -- the row number is just called "x" and since x and y are related, the value of x cannot change except explicitly.
