SQLite Forum

Non primary key alias to rowid
Login
> And anyway rowid is not stable.

This is incorrect.  RowID is 100% absolutely and completely stable.  

The RowID changes if and only if _**YOU**_ change it.  

The ways that you may change it are with an UPDATE statement or by performing a VACUUM on a database which contains a table that has a non-preserved (non-explicitly named) RowID, or _**YOU**_ otherwise fail to preserve the RowID, for example when you dump/load data from a table.

The only way to make a table column behave like a RowID is to declare it as a rowid using the "INTEGER PRIMARY KEY" declaration.  You may also make it "AUTOINCREMENT" if you like, but there is an almost 100% absolute certainty that you do not need to use the AUTOINCREMENT keyword (which actually has nothing whatsoever to do with auto-incrementing -- rather it should be called MONOTONIC, as in "INTEGER PRIMARY KEY MONOTONIC" since that is what it actually does -- pehaps in your particular case this is what you are looking for).

Only "named columns" can be used in foreign key relationships so if you wish to use the "RowID" in a foreign key relationship you must explicitly declare it.  The contents of all "named columns" are automatically preserved on vacuum.

<https://sqlite.org/rowidtable.html>  
<https://sqlite.org/autoinc.html>