SQLite AutoIncrement

Posted by Joe Enos on April 18, 2021 · 1 min read

One of the interesting things in SQLite is the ROWID column, which is a special column created on all tables (with some exceptions) by default. This can be an alias for a defined column, as long as that columns is an INTEGER primary key column, or it can act as the primary key by itself.

But the ROWID behaves differently from SQL Server IDENTITY columns or Oracle sequences, because they can be reused - if you delete the record with the highest ROWID and then insert a new one, you'll get a repeat. If you're not using this value for any particular purpose, this might be just fine. But if you want a new ID with every new record, then use the AUTOINCREMENT indicator on the column when creating the table.

View code on GitHub