SQLite Forum

Non primary key alias to rowid
Tables declared as WITHOUT ROWID do not have a rowid; instead, they need to have a PRIMARY KEY declared.

In ROWID Tables, the undeclared names rowid, oid and _rowid_ all refer to the internal rowid, unless they are overloaded by a declared column. Additionally, the single column declared as INTEGER PRIMARY KEY can be added as an alias for the internal rowid.

Internal rowids are UNIQUE by design. As long as the row is not deleted, it retains its rowid. It is only when you delete a row by PRIMARY KEY and insert a new row with the same PRIMARY KEY that the rowid may appear to have changed.

For efficient use of a foreign key, it should be the PRIMARY KEY of the parent table (for fast lookup) and there should be an index on the child table (for enforcing ON DELETE etc. clauses). Make the latter index UNQIUE to allow not more than one entry in the child table; make the latter index NOT NULL to force all rows in the child table to have a parent.