Interger Primary Key as Rowid SQLite

Posted by Alexis on 17 January 2011 | 1 Comments

Tags:

Interesting quirk about SQLite table PRIMARY KEY's. I've been using ID as my table KEY name, a habbit adobted 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 searh. SQLite actually makes a seperate ROWID column by default for all tables that don't have an Interger Primary Key. Only if another column name is explictly 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 interger 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 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));
  • 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 INTERGER PRIMARY KEY,
CoachID INTERGER 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 INTERGER 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


Post your comment

Comments

  • This is very good to know

    Posted by C. P. Burns, 06/07/2011 4:08pm (6 years ago)

RSS feed for comments on this page | RSS feed for all comments