CODEWITHSUNDEEP
sqliteuuid
Mike Baxter
Mike Baxter

Reputation: 7258

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?

Upvotes: 129

Views: 140833

Answers (6)

fabiolimace
fabiolimace

Reputation: 1197

You can generate UUIDv7 using a view:

-- DROP VIEW IF EXISTS uuid7;
CREATE VIEW uuid7 AS
WITH unixtime AS (
    SELECT CAST((STRFTIME('%s') * 1000) + ((STRFTIME('%f') * 1000) % 1000) AS INTEGER) AS time
    -- SELECT CAST((UNIXEPOCH('subsec') * 1000) AS INTEGER) AS time -- for SQLite v3.38.0 (2022)
)
SELECT PRINTF('%08x-%04x-%04x-%04x-%012x', 
       (select time from unixtime) >> 16,
       (select time from unixtime) & 0xffff,
       ABS(RANDOM()) % 0x0fff + 0x7000,
       ABS(RANDOM()) % 0x3fff + 0x8000,
       ABS(RANDOM()) >> 16) AS next;

Usage:

sqlite> SELECT next FROM uuid7;
01901973-f202-71ca-9a22-14e7146dab85

You can also use an insert trigger to make your life easier:

-- DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (
    uuid TEXT PRIMARY KEY,
    created DATE NOT NULL,
    content TEXT NOT NULL,
    CONSTRAINT check_my_table_uuid CHECK (uuid REGEXP '[a-f0-9]{8}-([a-f0-9]{4}-){3}[a-f0-9]{12}')
);

-- DROP TRIGGER IF EXISTS trigger_after_insert_on_my_table;
CREATE TRIGGER trigger_after_insert_on_my_table
    AFTER INSERT ON my_table FOR EACH ROW WHEN NEW.uuid IS NULL
BEGIN
    UPDATE my_table SET uuid = (SELECT next FROM uuid7) WHERE ROWID = NEW.ROWID;
END;

NOTES:

  1. We are not using a NOT NULL in the UUID field, which will be populated by the trigger, like a DEFAULT keyword.
  2. The trigger generates a UUIDv7 when none is provided in the insert statement.
  3. The check constraint verifies if the provided UUID is valid on updates and inserts.
  4. Unfortunately, we can't use a sub-query as DEFAULT value for the PK. That's why we use the trigger.

GIST: https://gist.github.com/fabiolimace/e3c3d354d1afe0b3175f65be2d962523

Upvotes: 1

spaaarky21
spaaarky21

Reputation: 6858

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.

Upvotes: 81

DrGo
DrGo

Reputation: 511

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

Upvotes: 26

Idhem
Idhem

Reputation: 964

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

Upvotes: 11

shtolik
shtolik

Reputation: 1368

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

Upvotes: 3

CL.
CL.

Reputation: 180020

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

Upvotes: 80

Related Questions