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?

Criswell

Answer 1

Yes, there is a difference: INTEGER is a special case in SQLite, when the database does not create a separate primary key, but reuses the ROWID column instead. When you use INT (or any other type that "maps" to INTEGER internally) a separate primary key is created.

That is why you see sqlite_autoindex created for the INT primary key, and no index created for the one of type INTEGER: SQLite reuses a built-in indexing structure for the integer primary key, rendering the autoindex unnecessary.

That is why the INTEGER primary key is more economical, both in terms of storage and in terms of performance.

See this link for details.

Epanodos

Answer 2

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.

Schoolmistress

Answer 3

Just to add albeit implied already on the answers here. The INTEGER PRIMARY KEY column that you created is simply an alias for ROWID or _ROWID_ or OID. And if the AUTOINCREMENT keyword is added then every new record inserted is an increment of 1 of the last ROWID and the last ROWID is kept by an sqlite internal table named sqlite_sequence.

See link here and here

On the other hand if you declare a column as INT PRIMARY KEY sqlite create an automatic index (hence the sqlite_autoindex) to keep track of the value inserted in the primary key to make sure it is unique.

Lacielacing