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
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
Also I found the default ROWID is not returned in a
SELECT * statement. By defining an integer primary key you get the speed enhancements and the ID value returned.
The rowid value can be accessed using one of the special case-independent names “rowid”, “oid”, or “rowid”
A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly “INTEGER”
Other integer type names like
INT, BIGINT, SHORT INTEGER or
UNSIGNED INTEGER DOES NOT replace rowid as an alias.
INTEGER PRIMARY KEY DESC also fails to replace the defult column name, must be ASC.
INTEGER PRIMARY KEY ASC works!!! as do these more cryptic examples from SQLite
CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));
Its is not documented why DESC fails, only that “This quirk is not by design. It is due to a bug in early versions of SQLite. But fixing the bug could result in very serious backwards incompatibilities”. The SQLite developers feel that goofy behavior in a corner case is far better than a compatibility break, so the original behavior is retained.
These are some test I did on a table I was creating
SELECT * FROM Session_Coach WHERE _ROWID_=1
SELECT * FROM Session_Coach WHERE SessionID=33
Read the notes at the bottom of this page for detailed info http://www.sqlite.org/lang_createtable.html