sql - Difference between INT PRIMARY KEY and INTEGER PRIMARY KEY SQLite

Question

Is there any difference between INT PRIMARY KEY and INTEGER PRIMARY KEY when defining a schema for a table? When int primary key is used, I got sqlite_autoindex thing generated; when integer primary key , I got sqlite_sequence table generated. what's the difference? what side effects can have the first and second variants?

question from:https://stackoverflow.com/questions/20289410/difference-between-int-primary-key-and-integer-primary-key-sqlite

Answer 1

UPDATE: SQLite's ROWID column is now a 64-bit integer:

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

It is all explained in SQLite 3 documentation:

2.0 The INTEGER PRIMARY KEY

One exception to the typelessness of SQLite is a column whose type is INTEGER PRIMARY KEY. (And you must use "INTEGER" not "INT". A column of type INT PRIMARY KEY is typeless just like any other.) INTEGER PRIMARY KEY columns must contain a 32-bit signed integer. Any attempt to insert non-integer data will result in an error.

INTEGER PRIMARY KEY columns can be used to implement the equivalent of AUTOINCREMENT. If you try to insert a NULL into an INTEGER PRIMARY KEY column, the column will actually be filled with an integer that is one greater than the largest key already in the table. Or if the largest key is 2147483647, then the column will be filled with a random integer. Either way, the INTEGER PRIMARY KEY column will be assigned a unique integer. You can retrieve this integer using the sqlite_last_insert_rowid() API function or using the last_insert_rowid() SQL function in a subsequent SELECT statement.