Is an index needed for a primary key in SQLite?
INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a "CREATE UNIQUE INDEX" statement would). Such an index is used like any other index in the database to optimize queries. As a result, there often no advantage (but significant overhead) in creating an index on a set of columns that are already collectively subject to a UNIQUE or PRIMARY KEY constraint.
If an column is marked INTEGER PRIMARY KEY, it's actually around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value. This is because:
...all rows within SQLite tables have a 64-bit signed integer key that uniquely identifies the row within its table ... Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.
With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid.
Such a column is usually referred to as an "integer primary key". APRIMARY KEY column only becomes an integer primary key if the declaredtype name is exactly "INTEGER". Other integer type names like "INT" or"BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primarykey column to behave as an ordinary table column with integer affinityand a unique index, not as an alias for the rowid.
A database will always silently create an index for a unique primary key so it can internally check it is unique efficiently.
Having created it, it will use it when necessary.
It won't, of course, always be clustered, and you specify usually in the schema if you want it to be.