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

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 as a formatted, human-readable string 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 saves space and might 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 type isn't used and the column's behavior is determined entirely by its storage class. 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, Apple's NSUUID, or Swift's UUID.

Answer 2

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 3

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

Answer 4

I needed an implementation for UUID in sqlite, since it's not a native feature, so here is a trick that I came across in the internet. SQLite doesn't support UUID, so the idea is to create a function that would generate a UUID using the randomblob() function

select lower(hex( randomblob(4)) || '-' || hex( randomblob(2))
         || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
         || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
         substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6))) 

This will ensure that you will have a UUID that can be stored in your table as varchar, so now to implement it. SQLite doesn't store functions, so you can use a trigger that can be called once a new record is inserted in your table

CREATE TABLE UUID_TABLE(
   id varchar(500),
   name varchar(500) NOT NULL,
   CONSTRAINT name_unique UNIQUE (name),
   CONSTRAINT rid_pkey PRIMARY KEY (id)
);

and the trigger

CREATE TRIGGER AutoGenerateGUID_RELATION_3
AFTER INSERT ON UUID_TABLE
FOR EACH ROW
WHEN (NEW.relation_id IS NULL)
BEGIN
   UPDATE UUID_TABLE SET relation_id = (select lower(hex( randomblob(4)) || '-' ||      hex( randomblob(2))
             || '-' || '4' || substr( hex( randomblob(2)), 2) || '-'
             || substr('AB89', 1 + (abs(random()) % 4) , 1)  ||
             substr(hex(randomblob(2)), 2) || '-' || hex(randomblob(6))) ) WHERE rowid = NEW.rowid;
END;

So whenever a new row is inserted, by default a NULL value will be affected to the id, and after that the trigger will modify it to a new UUID value stored as varchar.

Solution inspired from: solution source

Answer 5

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:

hex(randomblob(16)) 

or

lower(hex(randomblob(16)))