SQLite AutoIncrement columns

SQLite

I'm using SQLite for data storage in the quick proof-of-concept sites. One thing that is convenient to have is autoincrement Id field so one does not have to write own code to maintain the uniqueness, having more time to focus on actual work that the application is supposed to do.

While I've been trying different keywords, from different suggestions over the web, it turns out the AutoIncrement functionality is already built in SQLite and kicks off automatically when the Id field is of type Integer. Also, bear in mind that Integer here is int64 so there's plenty of values for most of the common scenarios.

Short answer: A column declared INTEGER PRIMARY KEY will autoincrement.

See http://www.sqlite.org/faq.html#q1

Entity Framework

Since I am using Entity Framewok to work with the data in SQLite database (MVC Scaffolding makes it so easy to utilize this), I ran into an issue where inserting the first row works but any subsequent row will get rejected by the SQLite. The reason for this is that EF sends 0 (zero) in the Id field while SQLite expects NULL. See below link for more details.

http://stackoverflow.com/questions/936804/sqlite-with-entity-framework

The solution for this is to explicitely mark the Id field with AUTOINCREMENT. To do this, I use SQLite Manager, Mozilla Firefox plugin, which allows me to get the SQL DDL for the table, manually add the 'autoincrement', drop and recreate table by issuing SQL commands.

Also, in the EF model, I set the Id columns StoreGeneratedPattern value to 'Identity', while some even suggest to use 'Computed'.