Database / SQLite Interview questions
What is the difference between INTEGER PRIMARY KEY and a regular PRIMARY KEY in SQLite?
Declaring a column as exactly INTEGER PRIMARY KEY makes it a direct alias for the table's
internal rowid, meaning lookups by that column hit the B-tree's key directly — the fastest
possible access path. A PRIMARY KEY on a non-integer column (or declared with
WITHOUT ROWID), or a composite primary key across multiple columns, doesn't get this same
rowid-aliasing behavior.
CREATE TABLE fast_lookup ( id INTEGER PRIMARY KEY, -- aliases rowid: fastest lookups name TEXT ); CREATE TABLE composite_key ( a TEXT, b TEXT, PRIMARY KEY (a, b) -- composite key; no rowid alias );
This distinction matters for performance-sensitive schema design: if a table's natural primary key is a
single integer, declaring it as INTEGER PRIMARY KEY gets you the fastest lookup path essentially
for free, whereas any other kind of primary key requires a normal (still efficient, but comparatively slower)
B-tree index traversal.
What special behavior does exactly `INTEGER PRIMARY KEY` trigger that other primary keys don't get?
Does a composite primary key across multiple columns get the same rowid-aliasing behavior?
More Related questions...
