Unique identifiers

Content:

  1. Introduction
  2. UUID
    1. Format
    2. Code
  3. ULID
    1. Format
    2. Code
  4. Database auto-increment
    1. Concept
    2. Code
  5. Database sequence
    1. Concept
    2. Code
  6. Database DIY
    1. Concept
    2. Code
  7. High-Low
    1. Concept
    2. Code
  8. Recommendation

Introduction:

Some data has a good natural key to be used when storing data.

But most data require an artificial key to be generated when storing data.

That makes generation of unique identifiers a relevant topic.

UUID:

UUID stands for Universally Unique IDentifier.

UUID's was defined by OSF back in the 1996.

Microsoft call them GUID (Globally Unique Identfier).

Despite the names UUID and GUID and various generating schemes (see different versions below), then there is no guarantee that they will always be unique. Theoretical it is possible to generate duplicate values. The probability of doing so is extremely small but greater than zero.

Format:

An UUID consists of 128 bit or 16 byte.

Traditional formatting is hex: 

12345678-1234-1234-1234-123456789012

(4 + 2 + 2 + 2 + 6 bytes)

There are different versions of UUID's.

Note that the content listings below are the logical content - not the way the data are stored in the 128 bit.

Version 1:

MAC address + time + counter.

Content:

Version 2:

MAC address + time + counter + security info. Intended for security purposes.

Content:

Version 3:

Hash of namespace and name.

Content:

Version 4:

Random.

Content:

Version 5:

Hash of namespace and name.

Content:

Version 6:

Similar to version 1 just with data ordered differently.

Content:

Version 7:

Content:

Version 8:

Custom implementation.

Content:

Note:

Variant is a weird field. It is actually only 2 bit for RFC 4122 but 1 bit for NCS compatibility and 3 bits for reserved by Microsoft and reserved for future. But everything here is RFC 4122 based.

Code:

Uid.java: 

import java.util.UUID;

public class Uid {
    public static void main(String[] args) {
        UUID t3 = UUID.nameUUIDFromBytes("UUID's are fun".getBytes());
        System.out.printf("%d %d : %s\n", t3.variant(), t3.version(), t3.toString());
        UUID t4 = UUID.randomUUID();
        System.out.printf("%d %d : %s\n", t4.variant(), t4.version(), t4.toString());
        UUID te = UUID.fromString("F3FCF979-7826-4DF9-A8B1-1060CFB6654D");
        System.out.printf("%d %d : %s\n", te.variant(), te.version(), te.toString());
    }
}

2 3 : f0d58447-791b-3640-ad42-2a685dd1607a
2 4 : 58b44915-723e-47f4-92f9-4e9cf55c2aa4
2 4 : f3fcf979-7826-4df9-a8b1-1060cfb6654d

There are also additional UUID libraries available for Java like uuid-creator.

Uid.java: 

import java.util.UUID;

import com.github.f4b6a3.uuid.UuidCreator;

public class Uid {
    public static void main(String[] args) {
        UUID t1 = UuidCreator.getTimeBased();
        System.out.printf("%d %d : %s\n", t1.variant(), t1.version(), t1.toString());
        UUID t3 = UuidCreator.getNameBasedMd5("UUID's are fun".getBytes());
        System.out.printf("%d %d : %s\n", t3.variant(), t3.version(), t3.toString());
        UUID t4 = UuidCreator.getRandomBased();
        System.out.printf("%d %d : %s\n", t4.variant(), t4.version(), t4.toString());
        UUID t5 = UuidCreator.getNameBasedSha1("UUID's are fun".getBytes());
        System.out.printf("%d %d : %s\n", t5.variant(), t5.version(), t5.toString());
        UUID t6 = UuidCreator.getTimeOrdered();
        System.out.printf("%d %d : %s\n", t6.variant(), t6.version(), t6.toString());
        UUID t7 = UuidCreator.getTimeOrderedEpoch();
        System.out.printf("%d %d : %s\n", t7.variant(), t7.version(), t7.toString());
    }
}

2 1 : f294ae0b-2373-11f0-8772-47a73c76660b
2 3 : f0d58447-791b-3640-ad42-2a685dd1607a
2 4 : c33c44d6-1e15-45d5-acc4-266b87993f2b
2 5 : b40d404e-94b0-5df1-9e61-b53403409b95
2 6 : 1f02373f-2975-6d65-958a-9beb7aa067f1
2 7 : 019677a6-aef8-7cd1-a904-1d38b009aa98

uid.cs: 

using System;

public class Program
{
    public static void Main(string[] args)
    {
        Guid t4 = Guid.NewGuid();
        Console.WriteLine("{0} {1} : {2}", t4.Variant >> 2, t4.Version, t4.ToString());
        Guid t7 = Guid.CreateVersion7();
        Console.WriteLine("{0} {1} : {2}", t7.Variant >> 2, t7.Version, t7.ToString());
        Guid te = Guid.Parse("F3FCF979-7826-4DF9-A8B1-1060CFB6654D");
        Console.WriteLine("{0} {1} : {2}", te.Variant >> 2, te.Version, te.ToString());
    }
}

2 4 : e6e782eb-8789-4bdc-ae27-2b7c2fb8d828
2 7 : 019677a7-c6a4-7934-9318-60d88d87d29d
2 4 : f3fcf979-7826-4df9-a8b1-1060cfb6654d

There are also additional UUID libraries available for .NET like DaanV2 (NuGet package DaanV2.UUID.Net).

uid.cs: 

using System;
using System.Text;

using DaanV2.UUID;

public class Program
{
    public static void Main(string[] args)
    {
        Guid t1 = V1.Generate();
        Console.WriteLine("{0} {1} : {2}", t1.Variant >> 2, t1.Version, t1.ToString());
        Guid t3 = V3.Generate(Encoding.UTF8.GetBytes("UUID's are fun"));
        Console.WriteLine("{0} {1} : {2}", t3.Variant >> 2, t3.Version, t3.ToString());
        Guid t4 = V4.Generate();
        Console.WriteLine("{0} {1} : {2}", t4.Variant >> 2, t4.Version, t4.ToString());
        Guid t5 = V5.Generate(Encoding.UTF8.GetBytes("UUID's are fun"));
        Console.WriteLine("{0} {1} : {2}", t5.Variant >> 2, t5.Version, t5.ToString());
        Guid t6 = V6.Generate();
        Console.WriteLine("{0} {1} : {2}", t6.Variant >> 2, t6.Version, t6.ToString());
        Guid t7 = V7.Generate();
        Console.WriteLine("{0} {1} : {2}", t7.Variant >> 2, t7.Version, t7.ToString());
    }
}

2 1 : 5df91137-d553-138d-9437-d89ef344595d
2 3 : f0d58447-791b-3640-ad42-2a685dd1607a
2 4 : 8211fa72-8258-498c-b80e-7fda82166ced
2 5 : b40d404e-94b0-5df1-9e61-b53403409b95
2 6 : 038dd553-5e05-6810-8b10-d89ef344595d
2 7 : b7814a84-1d4b-7561-8000-00005e82de0c

uid.py: 

import uuid

def variant(v):
    if v == uuid.RESERVED_NCS:
        return 1
    if v == uuid.RFC_4122:
        return 2
    if v == uuid.RESERVED_MICROSOFT:
        return 3

t1 = uuid.uuid1()
print('%d %d : %s' % (variant(t1.variant), t1.version, str(t1)))
t3 = uuid.uuid3(uuid.uuid4(), "UUID's are fun")
print('%d %d : %s' % (variant(t3.variant), t3.version, str(t1)))
t4 = uuid.uuid4()
print('%d %d : %s' % (variant(t4.variant), t4.version, str(t4)))
t5 = uuid.uuid5(uuid.uuid4(), "UUID's are fun")
print('%d %d : %s' % (variant(t5.variant), t5.version, str(t5)))
te = uuid.UUID('F3FCF979-7826-4DF9-A8B1-1060CFB6654D')
print('%d %d : %s' % (variant(te.variant), te.version, str(te)))

2 1 : ae9d12d2-2374-11f0-af1a-9a61d5e0f852
2 3 : ae9d12d2-2374-11f0-af1a-9a61d5e0f852
2 4 : cfeefb0f-947a-4f1b-b76a-04cb83398fbd
2 5 : 08bb93b3-fb3a-5e87-a257-63b6e338084f
2 4 : f3fcf979-7826-4df9-a8b1-1060cfb6654d

GUID's are an integrated part of Windows COM.

Using RPC that is the DCE RPC foundation for DCOM.

uid.cpp: 

#include <iostream>

using namespace std;

#include <rpc.h>

static int variant(UUID *t)
{
    return t->Data4[0] >> 6;
}

static int version(UUID *t)
{
    return t->Data3 >> 12;
}

static void dump(UUID *t)
{
    RPC_STATUS stat;
    RPC_CSTR buf = NULL;
    stat = UuidToString(t, &buf);
    wcout << variant(t) << " " << version(t) << " : " << (TCHAR*)buf << endl;
    RpcStringFree(&buf);
}


int main()
{
    RPC_STATUS stat;
    UUID t4;
    stat = UuidCreate(&t4);
    dump(&t4);
    UUID te;
    stat = UuidFromString((RPC_CSTR)"F3FCF979-7826-4DF9-A8B1-1060CFB6654D", &te);
    dump(&te);
    return 0;
}

2 4 : e951bf62-484b-44f3-8380-e6b67285156f
2 4 : f3fcf979-7826-4df9-a8b1-1060cfb6654d

Using CoCreateGuid instead of UuidCreate, which according to som COM experts has some advantages even though CoCreateGuid supposedly use UuidCreate.

uid.cpp: 

#include <iostream>

using namespace std;

#include <rpc.h>
#include <objbase.h>

static int variant(UUID *t)
{
    return t->Data4[0] >> 6;
}

static int version(UUID *t)
{
    return t->Data3 >> 12;
}

static void dump(UUID *t)
{
    RPC_STATUS stat;
    RPC_CSTR buf = NULL;
    stat = UuidToString(t, &buf);
    wcout << variant(t) << " " << version(t) << " : " << (TCHAR*)buf << endl;
    RpcStringFree(&buf);
}


int main()
{
    RPC_STATUS stat;
    GUID t4;
    stat = CoCreateGuid(&t4);
    dump(&t4);
    return 0;
}

2 4 : c102384b-2faa-4e48-b7f1-277e255e8612

Boost has a uuid module.

uid.cpp: 

#include <iostream>

using namespace std;

#include <boost/uuid/uuid.hpp>
#include <boost/uuid/uuid_io.hpp>
#include <boost/uuid/uuid_generators.hpp>
#include <boost/uuid/name_generator_md5.hpp>

using namespace boost::uuids;

static int variant(enum uuid::variant_type v)
{
    if(v == uuid::variant_ncs) return 1;
    if(v == uuid::variant_rfc_4122) return 2;
    if(v == uuid::variant_microsoft) return 3;
    return -1;
}

int main()
{
    name_generator_md5 g3(ns::oid());
    uuid t3 = g3("UUID's are fun");
    cout << variant(t3.variant()) << " " << t3.version() << " : " << t3 << endl;
    random_generator g4;
    uuid t4 = g4();
    cout << variant(t4.variant()) << " " << t4.version() << " : " << t4 << endl;
    name_generator_sha1 g5(ns::oid());
    uuid t5 = g5("UUID's are fun");
    cout << variant(t5.variant()) << " " << t5.version() << " : " << t5 << endl;
    return 0;
}

2 3 : 2f20ac58-abad-3020-9812-7781e496e1f9
2 4 : cbebd86a-8820-456e-b347-35f2ac3f9580
2 5 : 0686630b-7fbd-5a27-8e29-75d22a8ffcdf

program uid;

uses
  SysUtils;

function variant(t : TGUID) : integer;

begin
   variant := t.D4[0] shr 6;
end;

function version(t : TGUID) : integer;

begin
   version := t.D3 shr 12;
end;

function strip(t : TGUID) : string;

var
  temp : string;

begin
   temp := GUIDToString(t);
   strip := Copy(temp, 2, Length(temp) - 2);
end;

function wrap(s : string) : string;

begin
   wrap := '{' + s + '}';
end;

var
  t4, te : TGUID;

begin
  CreateGUID(t4);
  writeln(variant(t4), ' ', version(t4), ' : ', strip(t4));
  te := StringToGUID(wrap('F3FCF979-7826-4DF9-A8B1-1060CFB6654D'));
  writeln(variant(te), ' ', version(te), ' : ', strip(te));
end.

2 4 : DBC91A1F-1F80-4E94-9120-B81E58A01503
2 4 : F3FCF979-7826-4DF9-A8B1-1060CFB6654D

MySQL has a UUID() function. 

mysql> SELECT UUID(); -- version 1 of type VARCHAR(36)
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| c44da408-238a-11f0-93ab-d89ef344595d |
+--------------------------------------+
1 row in set (0.00 sec)

PostgreSQL has a GEN_RANDOM_UUID() function. 

postgres=# SELECT GEN_RANDOM_UUID(); -- version 4 as BINARY(16)
           gen_random_uuid
--------------------------------------
 c12ff424-2278-46d7-9765-e5f78b3c22f8
(1 row)

and if extension uuid-ossp has been created also UUID_GENERATE_V1() and UUID_GENERATE_V4() functions. 

postgres=# SELECT UUID_GENERATE_V1(); -- version 1 as BINARY(16)
           uuid_generate_v1
--------------------------------------
 5619ff90-238b-11f0-8abc-a7f3dfef0d51
(1 row)


postgres=# SELECT UUID_GENERATE_V4(); -- version 4 as BINARY(16)
           uuid_generate_v4
--------------------------------------
 a10633a3-2249-44a3-803c-a57591a8fb71
(1 row)

MS SQLServer has NEWID() function. 

1> SELECT NEWID() -- version 4 of type uniqueidentifier
2> GO

 ------------------------------------
 3880AC0A-148F-40CE-9F68-9EABA5F4E411

(1 row affected)

Oracle DB has a SYS_GUID() function. 

SQL> SELECT SYS_GUID() FROM DUAL; -- custom version as datatype RAW(16)

SYS_GUID()
--------------------------------
6376425899E3412AA574EBAC6636EC2D

ULID:

ULID stands for Universally unique Lexicographically sortable IDentifier.

ULID was invented by Alizain Feerasta in 2016. ULID has gotten some traction in the web world. The canonical implementation is in JavaScript.

Format:

ULID's are similar to UUID's but intended to be sortable.

So an ULID consists of 128 bit or 16 byte.

Traditional formatting is base32 so 10 + 16 characters.

Content:

Code:

There are ULID libraries available for Java like ulid-creator.

Uid.java: 

import java.util.UUID;

import com.github.f4b6a3.ulid.Ulid;
import com.github.f4b6a3.ulid.UlidCreator;

public class Uid {
    public static void main(String[] args) {
        Ulid t = UlidCreator.getUlid();
        System.out.printf("%s\n", t.toString());
        Ulid te = Ulid.from("01D7CB31YQKCJPY9FDTN2WTAFF");
        System.out.printf("%s\n", te.toString());
    }
}

01JSW69YE6QG6SDCF9JPPTX6TN
01D7CB31YQKCJPY9FDTN2WTAFF

There are ULID libraries available for .NET like Cysharp Ulid (NuGet package Ulid).

uid.cs: 

using System;

public class Program
{
    public static void Main(string[] args)
    {
        Ulid t = Ulid.NewUlid();
        Console.WriteLine("{0}", t);
        Ulid te = Ulid.Parse("01D7CB31YQKCJPY9FDTN2WTAFF");
        Console.WriteLine("{0}", te);
    }
}

01JSW6NB63QJS4MRH813VE8PD3
01D7CB31YQKCJPY9FDTN2WTAFF

There are ULID libraries available for .NET like RobThree NUlid (NuGet package NUlid).

uid.cs: 

using System;

using NUlid;

public class Program
{
    public static void Main(string[] args)
    {
        Ulid t = Ulid.NewUlid();
        Console.WriteLine("{0}", t);
        Ulid te = Ulid.Parse("01D7CB31YQKCJPY9FDTN2WTAFF");
        Console.WriteLine("{0}", te);
    }
}

01JSW6P875V7Q1EGDBS5RR5GSQ
01D7CB31YQKCJPY9FDTN2WTAFF

There are ULID libraries available for Python like python-ulid (PyPi package python-ulid).

uid.py: 

from ulid import ULID

t = ULID()
print('%s' % (str(t)))
te = ULID.from_str('01D7CB31YQKCJPY9FDTN2WTAFF')
print('%s' % (str(te)))

01JSW6W5ZTNTSZ8NAJ1D5ARB8Y
01D7CB31YQKCJPY9FDTN2WTAFF
There are ULID libraries available for C++ like Suyash ulid (note that you only need the header files!).

uid.cpp: 

#include <iostream>

using namespace std;

#include "ulid.hh"

using namespace ulid;

int main()
{
    ULID t = CreateNowRand();
    cout << Marshal(t) << endl;
    ULID te = Unmarshal("01D7CB31YQKCJPY9FDTN2WTAFF");
    cout << Marshal(te) << endl;
    return 0;
}

0001M0WWPE027K3KMNF9CY9MDY
01D7CB31YQKCJPY9FDTN2WTAFF

The generated value seems a little off.

There are ULID libraries available for PHP like php-ulid (composer package robinvdvleuten/ulid).

uid.php: 

<?php

require 'vendor/autoload.php';

use Ulid\Ulid;

$t = Ulid::generate();
echo sprintf("%s\r\n", $t);
$te = Ulid::fromString('01D7CB31YQKCJPY9FDTN2WTAFF');
echo sprintf("%s\r\n", $te);

?>

01JSW779WMH4AFRWAFTQ1M6VTQ
01D7CB31YQKCJPY9FDTN2WTAFF

Database auto-increment:

Most databases (with a notable exception of Oracle DB) supports auto-increment (also known as identity) columns.

Concept:

An auto-increment/identity column is a column (typical of type INTEGER or BIGINTEGER) where the database creates and inserts a unique value, if an INSERT does not provide a value for that field.

Code:

In MySQL the column is declared with an AUTO_INCREMENT modifier.

The function LAST_INSERT_ID() return the last generated value. 

mysql> CREATE TABLE uiddemo
    -> (
    ->     id INTEGER AUTO_INCREMENT,
    ->     txt VARCHAR(255),
    ->     PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO uiddemo(txt) VALUES('AAAA');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO uiddemo(txt) VALUES('BBBB');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO uiddemo(txt) VALUES('CCCC');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT id,txt FROM uiddemo;
+----+------+
| id | txt  |
+----+------+
|  1 | AAAA |
|  2 | BBBB |
|  3 | CCCC |
+----+------+
3 rows in set (0.00 sec)

In PostgreSQL the column is getting a special type SERIAL.

The function LASTVAL() return the last generated value. 

postgres=# CREATE TABLE uiddemo
postgres-# (
postgres(#     id SERIAL,
postgres(#     txt VARCHAR(255),
postgres(#     PRIMARY KEY(id)
postgres(# );
CREATE TABLE
postgres=# INSERT INTO uiddemo(txt) VALUES('AAAA');
INSERT 0 1
postgres=# SELECT LASTVAL();
 lastval
---------
       1
(1 row)


postgres=# INSERT INTO uiddemo(txt) VALUES('BBBB');
INSERT 0 1
postgres=# SELECT LASTVAL();
 lastval
---------
       2
(1 row)


postgres=# INSERT INTO uiddemo(txt) VALUES('CCCC');
INSERT 0 1
postgres=# SELECT LASTVAL();
 lastval
---------
       3
(1 row)


postgres=# SELECT id,txt FROM uiddemo;
 id | txt
----+------
  1 | AAAA
  2 | BBBB
  3 | CCCC
(3 rows)

In MS SQLServer the column is declared with an IDENTITY modifier.

The function SCOPE_IDENTITY() return the last generated value. 

1> CREATE TABLE uiddemo
2> (
3>     id INTEGER IDENTITY,
4>     txt VARCHAR(255),
5>     PRIMARY KEY(id)
6> )
7> GO
1> INSERT INTO uiddemo(txt) VALUES('AAAA')
2> GO
(1 row affected)
1> SELECT SCOPE_IDENTITY()
2> GO

 ----------------------------------------
                                        1

(1 row affected)
1> INSERT INTO uiddemo(txt) VALUES('BBBB')
2> GO
(1 row affected)
1> SELECT SCOPE_IDENTITY()
2> GO

 ----------------------------------------
                                        2

(1 row affected)
1> INSERT INTO uiddemo(txt) VALUES('CCCC')
2> GO
(1 row affected)
1> SELECT SCOPE_IDENTITY()
2> GO

 ----------------------------------------
                                        3

(1 row affected)
1> SELECT id,txt FROM uiddemo
2> GO
 id
        txt                                                                                                             
                                                                                                                        

 -----------
        ----------------------------------------------------------------------------------------------------------------
        ----------------------------------------------------------------------------------------------------------------
        -------------------------------
           1
        AAAA                                                                                                            
                                                                                                                        

           2
        BBBB                                                                                                            
                                                                                                                        

           3
        CCCC                                                                                                            
                                                                                                                        


(3 rows affected)

In IBM DB2 the column is declared with a GENERATED BY DEFAULT AS IDENTITY modifier.

The function IDENTITY_VAL_LOCAL() return the last generate dvalue. 

db2 => CREATE TABLE uiddemo(id INTEGER GENERATED BY DEFAULT AS IDENTITY, txt VARCHAR(255), PRIMARY KEY(id));
DB20000I  The SQL command completed successfully.
db2 => INSERT INTO uiddemo(txt) VALUES('AAAA');
DB20000I  The SQL command completed successfully.
db2 => SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;

1
---------------------------------
                               1.

  1 record(s) selected.

db2 => INSERT INTO uiddemo(txt) VALUES('BBBB');
DB20000I  The SQL command completed successfully.
db2 => SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;

1
---------------------------------
                               2.

  1 record(s) selected.

db2 => INSERT INTO uiddemo(txt) VALUES('CCCC');
DB20000I  The SQL command completed successfully.
db2 => SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1;

1
---------------------------------
                               3.

  1 record(s) selected.

db2 => SELECT id,txt FROM uiddemo;

ID          TXT                                                                                                                                                                                                                                 
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
          1 AAAA                                                                                                                                                                                                                                
          2 BBBB                                                                                                                                                                                                                                
          3 CCCC                                                                                                                                                                                                                                

  3 record(s) selected.

The fact that the function to retrieve the last generated value is not standardized can hurt database programming portability between databases.

JDBC (Java and other JVM languages) can do it in a portable way, because the JDBC driver encapsulate it.

Demo: 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AutoIncrTrick {
    public static void test(Connection con) throws SQLException {
        try(PreparedStatement pstmt = con.prepareStatement("INSERT INTO uiddemo(txt) VALUES(?)", PreparedStatement.RETURN_GENERATED_KEYS)) {
            for(int i = 1; i <= 3; i++) {
                String txt = Character.toString(64 + i) + Character.toString(64 + i) + Character.toString(64 + i) + Character.toString(64 + i);
                pstmt.setString(1, txt);
                pstmt.executeUpdate();
                try(ResultSet rs = pstmt.getGeneratedKeys()) {
                    if(rs.next()) {
                        System.out.println(rs.getLong(1));
                    }
                }
            }
        }
    }
    public static void test(String constr, String un, String pw) throws SQLException {
        try(Connection con = DriverManager.getConnection(constr, un, pw)) {
            test(con);
        }
    }
    public static void test(String constr) throws SQLException {
        try(Connection con = DriverManager.getConnection(constr)) {
            test(con);
        }
    }
    public static void main(String[] args) throws SQLException {
        test("jdbc:mysql://localhost/Test", "root", "hemmeligt");
        test("jdbc:postgresql://localhost/postgres", "postgres", "hemmeligt");
        test("jdbc:db2://localhost:25000/Test", "arne", "hemmeligt");
        test("jdbc:sqlserver://localhost;database=Test;integratedSecurity=false;encrypt=false;", "sa", "hemmeligt");
    }
}

Database sequence:

Oracle DB and databases trying to copy Oracle DB has an alternative to auto-increment/identity columns called sequence.

Concept:

A sequence is separate entity capable of returning values.

Code:

SQL> CREATE TABLE uiddemo
  2  (
  3      id INTEGER,
  4      txt VARCHAR(255),
  5      PRIMARY KEY(id)
  6  );

Table created.

SQL> CREATE SEQUENCE demoseq;

Sequence created.

SQL> INSERT INTO uiddemo(id,txt) VALUES(demoseq.NEXTVAL,'AAAA');

1 row created.

SQL> SELECT demoseq.CURRVAL FROM dual;

   CURRVAL
----------
         1

SQL> INSERT INTO uiddemo(id,txt) VALUES(demoseq.NEXTVAL,'BBBB');

1 row created.

SQL> SELECT demoseq.CURRVAL FROM dual;

   CURRVAL
----------
         2

SQL> INSERT INTO uiddemo(id,txt) VALUES(demoseq.NEXTVAL,'CCCC');

1 row created.

SQL> SELECT demoseq.CURRVAL FROM dual;

   CURRVAL
----------
         3

SQL> SELECT id,txt FROM uiddemo;

        ID
----------
TXT
--------------------------------------------------------------------------------
         1
AAAA

         2
BBBB

         3
CCCC



postgres=# CREATE TABLE uiddemo
postgres-# (
postgres(#     id INTEGER,
postgres(#     txt VARCHAR(255),
postgres(#     PRIMARY KEY(id)
postgres(# );
CREATE TABLE
postgres=# CREATE SEQUENCE demoseq;
CREATE SEQUENCE
postgres=# INSERT INTO uiddemo(id,txt) VALUES(NEXTVAL('demoseq'),'AAAA');
INSERT 0 1
postgres=# SELECT CURRVAL('demoseq');
 currval
---------
       1
(1 row)


postgres=# INSERT INTO uiddemo(id,txt) VALUES(NEXTVAL('demoseq'),'BBBB');
INSERT 0 1
postgres=# SELECT CURRVAL('demoseq');
 currval
---------
       2
(1 row)


postgres=# INSERT INTO uiddemo(id,txt) VALUES(NEXTVAL('demoseq'),'CCCC');
INSERT 0 1
postgres=# SELECT CURRVAL('demoseq');
 currval
---------
       3
(1 row)


postgres=# SELECT id,txt FROM uiddemo;
 id | txt
----+------
  1 | AAAA
  2 | BBBB
  3 | CCCC
(3 rows)

Database DIY:

This is just trying to do it using standard SQL commands.

Concept:

There are basically two approaches: 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN
SELECT idfld FROM idtbl
UPDATE idtbl SET idfld = idfld + 1
COMMIT

and: 

BEGIN
SELECT idfld FROM idtbl FOR UPDATE
UPDATE idtbl SET idfld = idfld + 1
COMMIT

For more details about this problem and potential concurremcy issues see here.

Code:

The logical SQL are shown above. Not much point in showing them being executed in different programming languages and database API's.

High-Low:

Database generators can ensure uniqueness, but it also comes at a cost - it is slow to use the database to generate unique identifiers.

So in 1997 Scott Ambler came up with a solution to speed up generation of unique identifiers using a database: the High-Low/HiLo pattern/strategy/algorithm.

Concept:

The concept is really simple:

and if we assume low part allows for N unique values then it work like:

Note that a schema also has a limit M on how many high parts are possible.

Common formats are:

Code:

Some persistence framework has builtin support for High-Low:

Recommendation:

My recommendation is:

I think UUID has too much luggage compared to ULID.

Article history:

Version Date Description
1.0 April 29th 2025 Initial version
1.1 May 10th 2025 Add Delphi/Lazarus UUID example

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj