Using text as a primary key in SQLite table bad?

android database sqlite primary-key rowid
mpellegr picture mpellegr · Apr 18, 2014 · Viewed 25.4k times · Source

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?

Answer

laalto picture laalto · Apr 18, 2014

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.

Related questions

How do I view the SQLite database on an Android device?

I have a set of data in an SQLite database. I need to view the database on a device. How do I do that? I have checked in ddms mode. The data in file explorer is empty.

Read More
What are the best practices for SQLite on Android?

What would be considered the best practices when executing queries on an SQLite database within an Android app? Is it safe to run inserts, deletes and select queries from an AsyncTask's doInBackground? Or should I use the UI Thread? I …

Read More
Best way to work with dates in Android SQLite

I'm having some trouble working with dates on my Android application that uses SQLite. I have a couple questions: What type should I use to store dates in SQLite (text, integer, ...)? Given the best way to store dates how do …

Read More