Implementing MySQL style AUTOINCREMENT in SQLite3

; Date: Sat Jan 07 2012

Tags: MySQL »»»» SQLite3

Yesterday I wrote about implementing the MySQL enum datatype in SQLite3, and while that exploration turned out to be pretty simple someone tweeted a followup talking about how I needed to cover the AUTOINCREMENT feature as well.  Studying my code I realized that indeed it would be necessary.  Fortunately this came out to be very simple, much simpler than was implementing the enum datatype.

To start this off let's review what the MySQL style auto_increment does.  A typical schema might include

CREATE TABLE geog (
  id int(11) NOT NULL auto_increment,
..
);

With this table definition you add items to your database with:

INSERT INTO geog(id, ..) VALUES(NULL, ..);

Basically the idea is when you supply NULL for the value of an auto_increment field, the database picks the next available value which will be one greater than the largest existing value.  This feature is useful for auto-generating unique keys for your data.

Unfortunately SQLite3 doesn't support this syntax.  And in the code I posted yesterday I skipped over the auto_increment feature by specifying this in the schema:

CREATE TABLE geog (
  id int(11) NOT NULL ,
..
);

This does not at all implement the auto_increment feature, instead it requires that values be provided for the id column.  The code running my website instead specifies NULL so that the database will provide a value for id.  If you think about it, it's difficult to implement this from the code running the site because there's a race condition in which you could write a query to get the current maximum value in the id column, then generate a new id value, but what if there are two requests underway at the same moment attempting to add rows to this table.  The two could be retrieving the maximum id value at the same time, generate the same next value for id, then both insert entries in the database with the same id value, which would erase its value as a unique identifier.  It's much better for the database to do this for us.

Fortunately the SQLite3 documentation has an answer for us (see http://www.sqlite.org/autoinc.html) that's ready to use, but with a different syntax than MySQL's implementation.  So much for SQL being a standard language, eh?

With SQLite3 you do this in your schema:

CREATE TABLE geog (
  id INTEGER PRIMARY KEY AUTOINCREMENT, -- int(11) NOT NULL ,
..
);

It turns out that in SQLite3 there is a ROWID column on every table that actually could serve very well as a unique identifier for each row.  However adding a column as defined above aliases the ROWID column to be the one you name with the datatype INTEGER PRIMARY KEY AUTOINCREMENT.  The other thing you have to do is not declare a PRIMARY KEY in the table like so:

CREATE TABLE geog (
  id INTEGER PRIMARY KEY AUTOINCREMENT, -- int(11) NOT NULL ,
..
--   PRIMARY KEY  (`id`) --  AUTOINCREMENT,
..
);

That is, here I've commented out the PRIMARY KEY declaration, because id is already declared to be a PRIMARY KEY and SQLite3 doesn't like having two PRIMARY KEY specifications.

So, that's nice, but how do you use it?

sqlite> insert into geog(id, continent, country, state, city, url) values  (1, 'Georgia', '', '', '', '');
SQL error: foreign-key violation: geog.continent
sqlite> insert into geog(id, continent, country, state, city, url) values  (1, 'Asia', '', '', '', '');
sqlite> insert into geog(id, continent, country, state, city, url) values  (NULL, 'Asia', 'Japan', '', '', '');
sqlite> insert into geog(id, continent, country, state, city, url) values  (NULL, 'Asia', 'Japan', 'Tokyo', '', '');
sqlite> insert into geog(id, continent, country, state, city, url) values  (NULL, 'Asia', 'Japan', 'Tokyo', 'Tokyo', '');
sqlite> .dump geog
BEGIN TRANSACTION;
CREATE TABLE geog (
  id INTEGER PRIMARY KEY AUTOINCREMENT, -- int(11) NOT NULL ,
  continent text, --  enum('North America','South America','Central America','Carribean','Atlantic','Europe (West)','Europe (East)','Africa','Middle East','South Asia','East Asia','Asia','Pacific','Australia') NOT NULL ,
  country char(255) NOT NULL default '',
  state char(255) NOT NULL default '',
  city char(255) NOT NULL default '',
  url char(255) default NULL
--   PRIMARY KEY  (`id`) --  AUTOINCREMENT,
--   KEY `byContinent` (`continent`),
--   KEY `byCountry` (`country`),
--   KEY `byState` (`state`),
--   KEY `byCity` (`city`),
--   KEY `geogurl` (`url`)
);
INSERT INTO "geog" VALUES(1,'Asia','','','','');
INSERT INTO "geog" VALUES(2,'Asia','Japan','','','');
INSERT INTO "geog" VALUES(3,'Asia','Japan','Tokyo','','');
INSERT INTO "geog" VALUES(4,'Asia','Japan','Tokyo','Tokyo','');
CREATE TRIGGER ContinentTrigger BEFORE INSERT ON geog FOR EACH ROW
WHEN (SELECT COUNT(*) FROM geogContinents WHERE  continentName = new.continent) = 0 BEGIN
   SELECT RAISE(rollback, 'foreign-key violation: geog.continent');
END;
COMMIT;

The first insert command demonstrates that the trigger still works to limit the continent names to the values stored in geogContinents.  The following insert commands demonstrate using NULL to autogenerate id values, and then we dump the table showing the values that the database now contains.

The behavior is exactly what the code in my site expects.