Using text as primary key in SQLite table is bad?

Question

Is it wrong to have text as a primary key in a SQLite database? I heard this is bad for performance reasons, is that true? And will rowid be used as the actual primary key in this case?

Answer 1

Is it wrong to have text as a primary key in a SQLite database? I heard that this is bad for performance reasons, is that true?

I have never heard anyone use a row as the primary key in a table. For me (and I sincerely hope for others) a very ugly practice with very low productivity.

If you will use the string as the primary key, you need to think about the “few” things:

• Will a 3-character combination suffice?
• Or should I use 5 characters?

Here, each line should have the same format (of course, readability), and also be unique . Oh! Here is the next “copy” -> you need to create some “unique line generator” that will generate a unique identifier for line ^{1 2} .

And also there are the following issues to consider:

• Longer lines = automatically harder and harder to compare
• The size of the table increases dramatically because it is pretty clear that the row is much larger as a number
• The number of rows is crazy to use a row as the primary key if you table can contain 1000 rows

This is a more complex topic, but I would like to say that OK, for very small tables, you could use rows as the primary key (if that makes sense), but if you look at the disadvantages, this is a much more efficient technique for using the number as primary key!

And what is a conclusion?

I do not recommend using the string as the primary key. It has more disadvantages as advantages (does it really have any advantage?).

Using a number as a primary key is much better (I'm afraid to say which is better).

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

If you use the string as primary, not.

¹ _{In real lines, rarely unique.}

² _{Of course, you can say that you can create an identifier on behalf of an element in a string, but it is again spaghetti code (elements can have the same name).}

Answer 2

Is it wrong to have text as a primary key in a SQLite database? I heard this is bad for performance reasons, is that true?

From the point of view of correctness, the TEXT PRIMARY KEY all right.

In terms of performance, prefer the INTEGER keys. But, as with any performance issue, measure it yourself to see if there is a significant difference with your data and use cases.

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

Only an INTEGER PRIMARY KEY receives an alias with a ROWID . There are no other types of primary keys, and there will be an implicit integer rowid if WITHOUT ROWID not specified. Link

Answer 3

In the real world, using strings as a primary key has many advantages if we are talking about UUIDs. The ability to create an entity "passport" exactly at the time of its creation can greatly simplify the asynchronous code and / or distributed system (if we are talking about a more complex architecture of the mobile client / server).

As far as performance is concerned, I did not find any noticeable differences when running the test to perform 10,000 primary searches, since in fact the database indexes do not save and do not compare rows when starting indexed searches.

Answer 4

Yes, if you use TEXT, you get android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: TableName.ColumnName (code 1555)

SQLite has a session to insert and return the row identifier of the last inserted row if this insert was successful. else will return -1.

return maps to _ID, this is the reason they call the BaseColumns interface for the table

strange that insert call should return rowid and not boolean or so

I want TEXT PRIMARY KEY to appear in sqlite