SQLite Gotcha 2

Creating a table with a column of type INT PRIMARY KEY will not result in automatic assignment of a unique value into that column when new rows are added. Why not? In part because INT is not a type in SQLite (but you can use it and the column will have integer affinity but you have to provide the value) and partly because rowid assignment only occurs on columns defined as INTEGER PRIMARY KEY.

Take a look at the documentation on SQLite data types (http://www.sqlite.org/datatype3.html). You may also be surprised to discover that you can declare a column of one type then store data of a different type in it.

SQLite Gotcha

ROWID is a reserved word in SQLite which seems reasonable.. but it has a synonym OID so if you declare a table with a column called OID then select from that table without using the table name as a prefix, you will not get what you expect.

If you are using an ORM to abstract away the database then you may never realise what is going on..