SQLite AutoIncrement

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s