Can I use anything other than BIGINT as Primary Key data type in SQLite?

Question

I was psyched about the possibility of using SQLite as a database solution during development so that I could focus on writing the code first and dynamically generating the db at runtime using NHibernate's ShemaExport functionality. However, I'm running into a few issues, not the least of which is that it seems that SQLite requires me to use Int64 for my primary keys (vs, say, Int32 or Guid). Is there any way around this?

Note: I should specify that this is in the context of an app using NHibernate. It is not strictly speaking the case that one can't create a table in SQLite with an INT datatype, but the behavior when you save and retrieve the data seems to indicate that it's being stored and/or retrieved as Int64.

Answer 1

SQLite will let you use any field in your table as a PRIMARY KEY. Doing so will implicitly create a UNIQUE index on the field. This is then the field that you, as a developer, can consider to be the primary unique identifier for the field. It can be any supported SQLite data type (below).

SQLite will always create an implicit internal numeric identifier for every table. It will have several aliases including RowID, OID, and _ROWID_. If you create your primary key as INTEGER PRIMARY KEY then it will use the same field as your primary key and SQLite's internal numeric identifier.

SQLite doesn't have a concept of Int32 or Int64 or Guid data types. It only has four data types: INT, REAL, TEXT, and BLOB. When you run DDL against SQLite if you use anything other than these four identifiers, SQLite will use a set of rules to determine which type to use. Basically, Int32 and Int64 are treated as aliases of INT and end up doing the exact same thing.

Even once you've created the tables with the data types you mentioned for each field, all you set is the type affinity for that field. SQLite does not enforce data types. Any data can be put into any field regardless of the declared type. SQLite will use the type affinity to convert data if possible, so if you insert '123' as a text string into an INT field, it will store it as the number 123.

The only exception to the type affinity is INTEGER PRIMARY KEY FIELDS. Those must be integers.

Integers in SQLite are always stored with a variable length field. So depending on the size of the integer, you may actually get an Int32 back for some rows an Int64 for others, all within the same field. This depends on the wrapper you're using, in this case NHibernate (I guess with System.Data.SQLite).