91  Primary keys UUID or GUID

Universally unique identifier (UUID) or Globally Unique Identifier (GUID)

RFC 9562: Universally Unique IDentifiers (UUIDs)

91.1 Sqlite3 usage

91.1.1 Quick and dirty solutions with randomblob

Below solutions will generate random number resembling UUID but they are not real UUID numbers.

randomblob number column

If we use number as column data type, we could use randomblob function

select lower(hex(randomblob(16)));

randomblob text column

If we use text as column data type, we could use a set of randomblob function calls and string concatenation.

Following sql could be used to generate uuid in sqlite, taken from following stackoverflow answer.

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

This one could be used for default values also. It is a quick and dirty solution.

91.1.2 Extension uuid.c

Sqlite3 uuid functionality is an extension. This uuid.c extension module implements RFC-4122 UUIDs, added on 2020-01-22 to sqlite.

We need to download the extension file, compile it and load it in the sqlite. See How to compile and load extension documentation.

  1. Download last version of uuid.c file from sqlite3 web site.

  2. compile the extension.

linux/Mac

gcc -g -fPIC -shared uuid.c -o uuid.so

Windows

gcc -g -fPIC -shared uuid.c -o uuid.dll

  1. open sqlite3 a

    sqlite3

  2. load the extension

    .load ./uuid

  3. use uuid function

select uuid();

427acb05-68a3-41eb-9794-6e7c8fe639e1

In gui tools, load_extension function should be used.

select load_extension('/absolute/folder/extension_filename');

for example in my ubuntu computer, using dbeaver and uuid extension.

select load_extension('/home/atilla/Projects/my-courses/course-database/databases/sqlite-extensions/uuid');
select uuid();

91.2 other tutorials