Using UUIDs in SQLite

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?

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

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

Solution 3:

There is now an extension for sqlite that creates valid uuids as per https://sqlite.org/src/file/ext/misc/uuid.c

Solution 4:

Not sure about using it as default field, but if someone needs to generate unique value in sqlite query following approach suggested here can be used:

The randomblob(N) function return an N-byte blob containing pseudo-random bytes. If N is less than 1 then a 1-byte random blob is returned. Hint: applications can generate globally unique identifiers using this function together with hex() and/or lower() like this: