Integer Primary Key as Rowid in SQLite

by Alexis Hope,

An interesting quirk about SQLite table primary keys. Coming from MySQL I had been using ID INTEGER PRIMARY KEY AUTOINCREMENT as a habit, but it turns out SQLite primary keys only behave as a rowid alias under specific conditions.

This is worth getting right. Primary keys return SELECT and ORDER BY queries roughly twice as fast as a normal column search. SQLite creates a separate ROWID column by default for all tables that don’t have an integer primary key. Only when another column is explicitly declared as INTEGER PRIMARY KEY ASC will SQLite skip creating that default rowid column.

Also worth noting: the default ROWID is not returned in a SELECT * statement. By defining an integer primary key explicitly you get both the speed benefit and the ID value returned in queries.

Notes:

  • The rowid value can be accessed using the special case-insensitive 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 do not replace rowid as an alias

INTEGER PRIMARY KEY DESC also fails to replace the default rowid — it must be ASC.

These all work:

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));

It 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 concluded that preserving the quirky corner-case behaviour is preferable to a compatibility break.

These are some tests I ran 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);

All of the following 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);

This fails:

SELECT * FROM Session_Coach WHERE _ROWID_ = 1;

This works:

SELECT * FROM Session_Coach WHERE SessionID = 33;

See the SQLite CREATE TABLE documentation for the full specification.

Tags: