April 23rd, 2007
Unique primary keys are an important feature of relational databases, however coming from a MySQL environment, I found some small issues when looking at how SQLite automatically creates IDs for your rows. Basically this is just illustrating the autoincrement SQLite documentation.
Here’s the schema of a simple SQLite database with no ID column specified:
sqlite> CREATE TABLE foo (FIELD INTEGER);
With some data in our database you can SELECT using the automatic “rowid”:
sqlite> INSERT INTO foo (FIELD) VALUES (11); sqlite> INSERT INTO foo (FIELD) VALUES (22); sqlite> INSERT INTO foo (FIELD) VALUES (33); sqlite> SELECT rowid, FIELD FROM foo WHERE rowid = 3; 3|33
The following is an excerpt from the SQLite site detailing the algorithm used to produce the IDs:
If no ROWID is specified on the insert, an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert.
In practice this means the following happens (picking up from the previous example):
sqlite> DELETE FROM foo WHERE rowid = 3; sqlite> SELECT rowid,FIELD FROM foo; 1|11 2|22 sqlite> INSERT INTO foo (FIELD) VALUES (44); sqlite> SELECT rowid,FIELD FROM foo; 1|11 2|22 3|44
As you can see, the algorithm correctly creates the next consecutive ID, but this can create some problems if you want a one-time ID. For example an ID used in a URL pointing to an article. If you delete that article then you can’t have another article overwriting that ID, as any URLs using that ID will point to a wrong article, instead of a 404 error. You can change this default behaviour by specifying a ID field by default, as you may do in other databases:
sqlite> CREATE TABLE bar (id INTEGER PRIMARY KEY AUTOINCREMENT, FIELD INTEGER); sqlite> INSERT INTO bar (FIELD) VALUES (11); sqlite> INSERT INTO bar (FIELD) VALUES (22); sqlite> INSERT INTO bar (FIELD) VALUES (33); sqlite> DELETE FROM bar WHERE id = 3; sqlite> INSERT INTO bar (FIELD) VALUES (44); sqlite> SELECT * FROM bar; 1|11 2|22 4|44
When you explicitly state the attributes of the id field, a different algorithm is used and SQLite stores the last ID to work from when creating new IDs.