'Using text as a primary key in SQLite table bad?
Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true? And will the rowid be used as the actual primary key in such a case?
Solution 1:[1]
Is it bad to have text as a primary key in an SQLite database? I heard that it's bad for performance reasons, is this true?
From correctness point of view, TEXT PRIMARY KEY is all right.
From performance point of view, prefer INTEGER keys. But as with any performance issue, measure it yourself to see if there's a significant difference with your data and use cases.
And will the rowid be used as the actual primary key in such a case?
Only INTEGER PRIMARY KEY gets aliased with ROWID. Other kinds of primary keys don't, and there will be the implicit integer rowid unless WITHOUT ROWID is specified. Reference.
Solution 2:[2]
In real world, using strings as primary key has a lot of benefits if we are talking about UUIDs. Being able to create entity "passport" exactly at the moment of its creation can massively simplify asynchronous code and/or distributed system (if we are talking about more complex mobile client / server architecture).
As to the performance, I did not find any measurable difference when running a benchmark to perform 10000 primary key lookups, as in reality, database indexes neither store nor compare strings when running indexed searches.
Solution 3:[3]
Although this thread discusses INTEGER vs TEXT primary keys, for context, see Blob vs. Text for primary keys circa 2021 where SQLite creator Richard Hipp replies.
Solution 4:[4]
Yes, if you use TEXT you get android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: TableName.ColumnName (code 1555)
SQLite has session to insert and return the row ID of the last row inserted, if this insert is successful. else will return -1.
return is mapped to _ID , this is the reason they force you interface BaseColumns for the table
its strange that insert call has to return the rowid, instead of a boolean or so
I wish TEXT PRIMARY KEY capability was there in sqlite
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Segabond |
| Solution 3 | Ken Lin |
| Solution 4 | Bipin |