Interger Primary Key as Rowid SQLite

by Alexis Hope, 17 Jan 2011

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

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.

NOTES:

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

CASE 1

CREATE TABLE Session_Coach (
SessionID INTEGER PRIMARY KEY,
CoachID INTEGER KEY

)
INSERT INTO Session_Coach (SessionID, CoachID) VALUES (33,44)

The following statements work
SELECT * FROM Session_Coach WHERE rowid=1
SELECT * FROM Session_Coach WHERE _ROWID_=1
SELECT * FROM Session_Coach WHERE SessionID=33

CASE 2

CREATE TABLE Session_Coach (
SessionID int PRIMARY KEY ASC,
CoachID INTEGER KEY
)
INSERT INTO Session_Coach (SessionID, CoachID) VALUES (33,44)

Fails SELECT * FROM Session_Coach WHERE _ROWID_=1

Works 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