In SQLite, the primary key column get auto-incremented by default. So, my thought was that there was no necessity to set the autoincrement flag explicitly in the create table DDL. Then I discovered a hard truth. When I don’t set the autoincrement flag explicitly, it does auto-increment, but there is a minor issue. It so happens that SQLite has two algorithms to perform auto-increment in primary keys – a default one where you don’t explicitly set the flag and an other one where you explicitly set the flag.

To understand the glitch, let’s consider an example. Assume that you have a table with 5 rows with IDs 1, 2, 3, 4 and 5. The default algorithm increments the highest value in the ID column by 1. If you add a new row to this table, it automatically gets an ID of 6. Now, let’s delete the row with ID #6. You are left in the same state you started – 5 rows with IDs 1, 2, 3, 4 and 5. Let’s add a new row once again. This new row again gets an ID of 6.

This may have disastrous consequences or no consequences at all depending on your scenario. Generally, the autoincrement behavior that is expected out of a database is not this. I would expect the last row to get an ID of 7 instead since the PK column will not be reused. That brings us to the other algorithm. If I explicitly set the autoincrement flag in the create table DDL, the row that was added last correctly gets an ID of 7 instead of 6. This behavior is consistent with other databases. Not a big issue at all depending on your scenario. But, just be warned.