Interger Primary Key as Rowid SQLite
Interesting quirk about SQLite table PRIMARY KEY’s. I’ve been using ID as my table KEY name, a habit adopted from MySQL. And in recent SQLite development I’ve been using
ID INTEGER PRIMARY KEY AUTO_INCREMENT;
But it turns out SQLite column PRIMARY KEY’s are only created under specific conditions.
This is incredibly important to get right as primary keys return SELECT or SORT querys about twice as fast as a normal column search. SQLite actually makes a separate ROWID column by default for all tables that don’t have an Integer Primary Key. Only if another column name is explicitly stated as below, will SQLite not make a default ROWID column (take note of ASC). INTEGER PRIMARY KEY ASC