SQLite Forum

Non primary key alias to rowid
PRIMARY KEY is just an alternate spelling for UNIQUE *EXCEPT* that in a RowID table the specific declaration INTEGER PRIMARY KEY spelled EXACTLY like that means that you have "named" the RowID column and it is now a "named column" and behaves like any other named column EXCEPT that it is the automatically assigned RowID (cannot be null).

The actual primary key of a RowID table is the RowID which may be either magical (not named and therefore cannot be used in FOREIGN KEY constraints and subject to "not being preserved" if you VACUUM the table or dump/load or "invisible" if you SELECT * from the table).  INTEGER PRIMARY KEY (the rowid) is also never NULL.  If you attempt to store a NULL value in that column it will be autoassigned the next available integer value.

You can have the RowID be a "named column" (after which it behaves like a named column with a meaningful value that can be used in FOREIGN KEY constraints and is preserved when the table is vacuumed and is returned by SELECT * just like other non-magical/non-hidden/non-invisible columns).

In a RowID table any column that is a PRIMARY KEY but not an INTEGER PRIMARY KEY (of which there can only be one, since it is the RowID) is merely a UNIQUE constraint and it is not constrained NOT NULL unless you specify that constraint.