Replacing sqlite.net "bigint" with integer for autoincrement primary key constraint

Question

We are using sqlite for our mobile project - however when we try to use our long id variables as primary key autoincrement - the code is giving error "Autoincrement is only allowed in integer primary key". When I checked online about this error, I found out that autoincrement in sqlite is not allowed for bigint types.

The interesting thing is bigint is implemented by integer as well - sqlite has no bigint type - it just uses the size of the integer to decide whether it is a bigint or not. http://www.sqlite.org/datatype3.html

To fix this - I replaced the open source code where it creates the table with "bigint" with "integer", and wrote some test code to verify that it works outside the boundaries of regular integer (added an item with id larger than the integer range on purpose and continuously added 10 more objects).

It seems like it is working now - yet I want to know if it can cause some other issues. We are syncing the mobile app ids to our db ids, and thus we will definitely have ids larger than the normal integer range.

Is this solution a valid solution? What kind of troubles could this cause?

Answer 1

From Datatypes In SQLite Version:

If the declared type contains the string "INT" then it is assigned INTEGER affinity.

However, this does not mean that a column with INTEGER affinity is suitable for auto-increment duty! The special case is explicitly drawn out in ROWIDs and the INTEGER PRIMARY KEY:

.. A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

The maximum size of an INTEGER value in SQLite is 8 bytes (64bits, signed two's complement) and thus maps cleanly to a long value in .NET (or Java); it doesn't matter if the column was declared as "INTEGER", which it must be for an auto-increment column, or with "BIGINT". (The actual type is per value, not per column; however, auto-increment/ROWID values will all be integers.)

Also, SQLite automatically has a "record/row identifier", even without explicitly creating such a column - this is the "ROWID" column and can be accessed as ROWID, _ROWID_, or OID unless shadowed. See SQLite Autoincrement if this is a suitable option, as it changes the algorithm and removes some monotonically increasing guarantees:

These are important properties in certain applications. But if your application does not need these properties, you should probably stay with the default behavior since the use of AUTOINCREMENT requires additional work..