mpellegr mpellegr - 1 month ago 12
Android Question

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?

Answer 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?

I never heard that somebody used string as primary key in table. For me (and I honestly hope also for others) very "ugly" practise with very low performance.

If you'll use string as primary key you needs to think about a "few" things:

  • Will be combination of 3 symbols enough?
  • Or should I use 5 symbols?

Here, each row must have same format (readability issue of course) and also be unique. Oh! Here is next "piggy work" -> you'll need to create some "unique string generator" that will generate unique1 string identificator2.

And also there are next issues is good to consider:

  • Longer strings = automatically harder and harder to compare
  • Size of table radically raises because it's pretty clear that string has much more size as number
  • Number of rows - it's madness to use string as primary key if you table can have 1000+ rows

It's more complex theme but i would like to say that OK, for very small tables would be possible to use strings as primary key (if it makes a sence) but if you'll look at disadvantages it's much more better technique to use number as primary key for sure!

And what is conclusion?

I don't recommend you to use string as primary key. It has more disadvantages as advantages (it has really some advantage?).

Usage of number as primary key is much more better (I'm scared to say the best) practise.

And will the rowid be used as the actual primary key in such a case?

If you will use string as primary not.

1In real strings are rarely unique.

2Of course, you could say that you can create identificator from name of item in row, but it's once again spaghetti code (items can have same name).