Using UUIDs in SQLite

Question

Is it possible to use UUID values as a primary key in SQLite? I'm finding extremely limited information on the topic, so I'm not sure if SQLite even supports a UUID data type. Should I be storing a UUID as a string?

Answer 1

SQLite allows to use any data type as primary key.

UUIDs can be stored either as strings (which are human-readable) or as 16-byte BLOBs (which might be faster if the records are so small that the difference matters).

Answer 2

CL's answer is correct but kind of skirts the issue at hand. As mentioned, a column (or multiple columns) of any type can be used as a primary key. So you could store the UUID in the formatted, human-readable string format and make that your table's key. And since a UUID is just a 128-bit integer, you could also store the integer's bytes as a BLOB, which I imagine would be slightly faster.

But to more directly answer what I believe is the question at hand, no, SQLite does not have any features that directly support UUID's. When SQLite creates a table, it uses a column's declared type to determine which of the five underlying storage classes (integer, real, text, blob or null) it will use. After the table is created, a column's declared type isn't used. So there are no UUID-specific column types or storage classes. There also don't seem to be any functions available for converting to and from a formatted UUID string. To get your UUID's bytes, you'll want to see what methods are provided by the language your application is written in. For example, Java's UUID class or Apple's NSUUID.