How can (or should) I map `BaseColumns._ID` to `ROWID`, `_ROWID_`, or `OID`?

Question

I'm using BaseColumns to create a databases with sqlite on Android. This class comes with a final variable _ID = "_id"; to (I think) standardize the name of a column of row id's SO answer about this. However sqlite usually does not need this, since it internally maintains a row id, accessible as any other column through the names ROWID, _ROWID_, or OID.

For me, it seems logic to map BaseColumns._ID to one of this virtual columns, however since its a final variable, I don't see a straightforward approach.

How can (or should) I map BaseColumns._ID to ROWID, _ROWID_, or OID?

EDIT: According to sqlite documentation:

"If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context."

And so, explicitly declaring an INTEGER PRIMARY column would be a duplication.

Answer 1

An alias is just another name to a column, it won't duplicate any data.

This means that creating an "_id" column with INTEGER PRIMARY KEY in Sqlite will not duplicate any data, as this will be "mapped" to rowid (i.e., will be an alias).

There is at least one difference between the "_id" alias and a regular alias (as pointed out by CL., quoting Sqlite documentation): "The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.". So creating an "_id" column with INTEGER PRIMARY KEY will prevent this column from being changed with a VACUUM command.

Answer 2

The rowids might be maintained internally, but they can change, e.g. when doing VACUUM.

To ensure that you have stable IDs, you should explicitly declare an INTEGER PRIMARY column — and then you can just use the name _id for it.