Does SQLite automatically create an index for a INTEGER PRIMARY KEY column?
(1) By bitxianaiz on 2020-09-26 18:11:05 [link] [source]
Quick SQLite-specific question... Say, if I create this table... CREATE TABLE foo ( foo_id INTEGER PRIMARY KEY, fname TEXT, lname TEXT ); ...will SQLite automatically create a primary key index for the foo_id column or do I have to manually create that index? I'm asking because sqlite_master is not showing an index... sqlite> SELECT * FROM sqlite_master; type name tbl_name rootpage sql ----- ---- -------- -------- ----------------------------------------------------------------------- table foo foo 2 CREATE TABLE foo ( foo_id INTEGER PRIMARY KEY, fname TEXT, lname TEXT ) However, if I create a similar table with a PRIMARY KEY (without an INTEGER column)... CREATE TABLE bar ( bar_id TEXT PRIMARY KEY, fname TEXT, lname TEXT ); ...then, the primary key index is automatically created by SQLite... sqlite> select * from sqlite_master; type name tbl_name rootpage sql ----- ---- -------- -------- ----------------------------------------------------------------------- table bar bar 516381 CREATE TABLE bar ( bar_id TEXT PRIMARY KEY, fname TEXT, lname TEXT ) index sqlite_autoindex_bar_1 bar 516382
(2) By Richard Hipp (drh) on 2020-09-26 18:18:07 in reply to 1 [source]
An INTEGER PRIMARY KEY becomes the actual key used in the B-tree that stores your table. So no index is required for efficient operation.
You should always omit indexes on PRIMARY KEY columns, regardless of the type. The best case for an index on a PRIMARY KEY is that it will be a no-op. The worst case is that it will make things run slower. So avoid the worst-case and just leave it off.
(3) By bitxianaiz on 2020-09-26 18:36:17 in reply to 2 [link] [source]
Got it. Thank you!
(4) By Keith Medcalf (kmedcalf) on 2020-09-26 19:59:19 in reply to 3 [link] [source]
By the same token, you should also omit explicit declaration of indexes on columns declared unique or unique constraints in the table definition. SQLite3 will create these indexes automatically.
(5) By bitxianaiz on 2020-09-26 20:33:10 in reply to 4 [link] [source]
Thank you