I have been working once again with SQLite, and once again I hate some of its dirty corners…. In this episode I’ll be talking about AUTOINCREMENT PRIMARY KEYS, hurray!!!! :(
The case
It is very common to create tables that use IDs as primary keys to identify the row in the table. In some/most cases you don’t really care the ID number that is used, you just want it to be unique. Using the most basic SQL possible we could create a table with such ID using:
CREATE TABLE test(id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255));
The above code should create an autoincrement id that is set by the database engine.
SQLite special use
SQLIte is an interesting DB. I’ve had problems with it before with triggers and now with AUTOINCREMENT IDs… This is the special case that SQLite has(from the official page):
When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example:
CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
If you read carefully you will get to the same conclusion I had… using AUTOINCREMENT columns is useless and redundant, that columns is already there!!!!! and on top of that we have that:
The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing. These are important properties in certain applications. But if your application does not need these properties, you should probably stay with the default behavior since the use of AUTOINCREMENT requires additional work to be done as each row is inserted and thus causes INSERTs to run a little slower.
Hmm so is normally worse to use AUTOINCREMENT… any clue when I shoudl use AUTOINCREMENT in SQLite?
