SQLite Forum

integer primary key and index table
Login

integer primary key and index table

(1) By sqlite3_preupdate_count (XiongZaiBingGan) on 2021-03-13 07:56:38 [source]

The document says 'integer primary key ' is an alias for the rowid.so if a column set 'integer primary key',does sqlite create a index table to mapping this column and rowid columm?

select * from table1 where col = 123; if col is integer primary key,does this select statement do a binary search in index-table for rowid first´╝îthen use the rowid to do binary search for row data in table1?

(2.2) By Keith Medcalf (kmedcalf) on 2021-03-13 08:24:03 edited from 2.1 in reply to 1 [link] [source]

Did you read https://sqlite.org/rowidtable.html?

if col is integer primary key,does this select statement do a binary search in index-table for rowid first

No. The rowid is the key to the table row in the b-tree. In the case you describe above the rowid is used to traverse the table b-tree directly.

Additional indexes contain the "key columns" and the rowid. The key columns are used to traverse the index b-tree to find the rowid of the row in the underlying table, and then that rowid is used directly to traverse the table b-tree to locate the row data.

See also https://sqlite.org/fileformat.html

(3.1) By Keith Medcalf (kmedcalf) on 2021-03-13 08:31:21 edited from 3.0 in reply to 1 [link] [source]

does sqlite create a index table to mapping this column and rowid columm

No. The integer primary key is the rowid. If you do not declare a rowid (integer primary key) for a rowid table, then one exists anyway, it just does not form part of the persistent data of the table -- it is invisible and subject to change at a whim -- but the rowid may be accessed by the magic name rowid, _rowid_, or oid provided that those names have not been declared elsehow in the rowid table definition.