Difference between INT PRIMARY KEY and INTEGER PRIMARY KEY SQLite
sql, sqlite
Solution
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.
Problem
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?