36

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?

mpellegr
  • 2,952
  • 3
  • 20
  • 36

6 Answers6

49

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.

laalto
  • 144,748
  • 64
  • 275
  • 293
31

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.

Segabond
  • 1,003
  • 11
  • 15
  • 1
    I would say that UUIDs consists of limited number of symbols (VARCHAR). So it is somewhat multi-byte integer, you can think about it as an extension to char, word, int, long,.... Where TEXT has a variable length and is a different story. – Nikolay Nov 11 '21 at 15:47
0

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.

Ken Lin
  • 1,537
  • 19
  • 17
-1

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

Bipin
  • 318
  • 2
  • 6
-4

A field of type PRIMARY KEY implies comparing values. Comparing a number is simpler than comparing a text.

The reason is that there is a specific assembly instruction for 64 bit numeric comparison. This will always be much faster than comparing text which in theory can be unlimited in size.

Example comparing number:

CMP DX, 00  ; Compare the DX value with zero
JE  L7      ; If yes, then jump to label L7
.
.
L7: ...

Read more about CMP assembly instruction here: https://www.tutorialspoint.com/assembly_programming/assembly_conditions.htm

Knowing this allows us to know that numbers will always be more performative (at least in the computing we have today).

Sergio Cabral
  • 5,588
  • 33
  • 35
-39

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).

Simon Dorociak
  • 33,095
  • 10
  • 66
  • 104
  • 4
    does the fact that my string comes from a server, is guaranteed to be unique, and is always a set length change this answer? – mpellegr Apr 18 '14 at 20:47
  • @mpellegr generally, an usage of string as primary key is not good practise how i wrote in my answer ;) – Simon Dorociak Apr 18 '14 at 22:32
  • 1
    I'am about to create a table that has a text column that is unique, indexed, and every query to that table will use it to reference the rows. The table is not planned to be ever referenced with foreign key. I almost added an integer ID column just from routine when the question came to my mind, why should I? So, in this special case is there any reason to create an ID column? – FPGA warrior Jun 22 '16 at 12:20
  • 5
    Were you able to measure the "very low performance" associated with a textual primary key ? Because the answers to [this question](http://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) (very similar to the current one except for the SQLite part) do not mention such an issue. – Faibbus Dec 14 '16 at 14:07
  • 1
    All good and valid points, but there is one edge case where an incrementing integer is just going to cause issues, and that is when you need to keep data in sync across multiple devices and a master data store. And GUID's are unique enough. – LordWabbit May 25 '20 at 05:25
  • Longer strings are harder to compare? This may be irrelevant if you have long strings in your table already that need to be unique. – Oddthinking Jun 28 '20 at 05:17
  • "Size of table radically raises"? If you need to store these strings in your table anyway, this is irrelevant. – Oddthinking Jun 28 '20 at 05:18
  • "it's madness to use string as primary key if you table can have 1000+ rows" That's begging the question. Why is it madness? – Oddthinking Jun 28 '20 at 05:18
  • "In real strings are rarely unique." This seems to be a bad assumption. If the string *is* guaranteed to be unique from the domain, your entire answer is undermined. – Oddthinking Jun 28 '20 at 05:19
  • I don't see anyone here providing any real reasons why this is bad practice. If you have UUIDs as the primary identifier for a table row, use it as your primary key. – Gregory Ray Jan 18 '21 at 19:17
  • Have a look at a database called CouchDB, it's a document oriented database and the document IDs (primary keys) are always saved as text. – Tiago Stapenhorst Martins Sep 16 '21 at 14:12