Some SQLite Rowid Issues

Rowid is a special, hidden column added to each SQLite database table (and to many more RDBMSes), which uniquely refer to a row. But, there are some things about rowid, that some people may not be aware of.

I know, that for some this could be a trivial. But it was not so obvious to me and since this is my blog, I can decide, what will be published here, right? :]

Things that you probably know about rowid:

• rowid is unique per every table, so you can’t have two records (rows) in one table, with the same rowid,
• it’s always there, no matter, if you use it or not,

Things not so obvious to everyone:

• rowid can change in particular circumstances (!); see below for details
• rowid isn’t returned automatically with select query; you need to request it explicitly (SELECT rowid,* FROM ...)
• you can have your own column named rowid, oid or _rowid_ (containing your own values):
  • this is possible in SQLite (no conflict or syntax error)
  • but if you use all three reserved names as your own columns
  • then there will be no way to get to “real” rowid
• in SQLite you can use rowid, oid or _rowid_ as column names to refer unique row identifier (details)

The first one sounds like a nasty one. Thus, if you want to rely some logic on rowid, assuming it as an independent, unchangeable row identifier, then you may get yourself into a serious troubles.

In this case you should consider declaring your own column, that will mimic functionality of “original” rowid, but will not share it ability to change.

Using INTEGER PRIMARY KEY AUTOINCREMENT as that column’s type sounds like simplest solution.

Refer to sqlabs.com blog for more details on this issue.