Android: Use UUID as primary key in SQLite

Is it a bad idea to use a UUID (String / Varchar) as primary key in a android sqlite database table instead of an integer that would be auto incremented?

The only for-certain problem that I can think of is that you will not be able to use CursorAdapter and its subclasses for displaying the results of queries on that table. CursorAdapter requires a unique integer _id column in the Cursor, and presumably you will not have one of those. You would have to create your own adapter, perhaps extending BaseAdapter, that handles it.

I guess there would be performance issues by using strings (a UUID has 36 characters) as primary key.

Possibly, but I will be somewhat surprised if it turns into a material problem on device-sized databases.

However I cant see any other possibility to implement such a collaborative syncing system, so I must use UUID, right?

You need some sort of UUID for your network protocol. Presumably, you will need that UUID in your database. Whether that UUID needs to be the primary key of a table, I can’t say, because I don’t know your schema.

Another possibility would be to use a integer auto increment primary key and to use a second column uuid. So to work on the users local device, i would use this primary key (integer) for JOINS etc., while I would use the uuid column for syncing with the other users.

Correct. You would have a UUID->local integer ID mapping table, use the UUIDs in your network protocol, and keep the local database mostly using the local integer IDs. Whether or not this will be a significant performance improvement (particularly given the increased database schema complexity), I can’t say.

What do you guys think about that approach or is it in your opinion to much work, since you wont expect a big significant performance issue by ussing UUID directly as primary key?

IMHO, either run some performance tests so you get some concrete comparable data, or only worry about it if your database I/O seems sluggish.

Leave a Comment

tech