SQL Reference ยท Constraints and Data Types
SQL UUIDs
Reference for storing UUIDs in SQL: native UUID types, text versus 16-byte binary, generating values in each database and the cost of random keys.
A UUID (universally unique identifier) is a 128-bit value, written as 32 hexadecimal digits in five groups, for example 123e4567-e89b-12d3-a456-426614174000. Systems generate UUIDs independently without asking a central counter, so two servers, or a mobile app that is offline, can create ids that will not collide. That makes them popular as primary keys, public identifiers and idempotency keys.
Support differs. PostgreSQL and (via UNIQUEIDENTIFIER) SQL Server have a native type. MySQL, Oracle and SQLite do not, so you store a UUID as 36-character text or as 16 bytes of binary. SQLite has no UUID function at all; you build one from random bytes.
CREATE TABLE sessions ( id TEXT PRIMARY KEY, -- or BLOB for 16 bytes created_at TEXT NOT NULL );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
The values differ on every run, but the shape does not: 32 hex digits become 36 characters once the four hyphens are inserted. This example builds random bytes only; it does not set the version and variant bits that mark an RFC 4122 version 4 UUID, so use a library or a database function for real identifiers.
Syntax
column_name UUID -- PostgreSQL column_name UNIQUEIDENTIFIER -- SQL Server column_name BINARY(16) -- MySQL column_name BLOB -- SQLite (16 bytes) column_name TEXT -- any database (36 characters)
| Form | Size | Notes |
|---|---|---|
| Native type | 16 bytes | Compact, validated, displayed in the hyphenated form. |
BINARY(16) / BLOB |
16 bytes | Compact, but you convert to and from text in queries. |
Text (CHAR(36)) |
36+ bytes | Readable, about twice the size, and costs more index space. |
Dialect differences
| Database | Native type | Generate a random UUID | Notes |
|---|---|---|---|
| SQLite | None | Build from randomblob(16) |
There is no built-in uuid() function. |
| PostgreSQL | UUID |
gen_random_uuid() (13+) |
PostgreSQL 18 adds uuidv7() for time-ordered ids. |
| MySQL | None (BINARY(16)) |
UUID() |
UUID_TO_BIN(x, 1) swaps time fields so ids sort roughly by time; BIN_TO_UUID() reverses it. |
| SQL Server | UNIQUEIDENTIFIER |
NEWID() |
NEWSEQUENTIALID() as a column default gives increasing values. |
| Oracle | None (RAW(16)) |
SYS_GUID() |
Returns 16 raw bytes. |
-- PostgreSQL CREATE TABLE sessions (id UUID PRIMARY KEY DEFAULT gen_random_uuid()); -- MySQL CREATE TABLE sessions (id BINARY(16) PRIMARY KEY DEFAULT (UUID_TO_BIN(UUID(), 1))); -- SQL Server CREATE TABLE sessions (id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID());
Usage notes
- Random keys fragment indexes. A fully random version 4 UUID inserts into random places of a B-tree, which causes page splits and cache misses on large tables. Time-ordered variants (UUIDv7, MySQL's swapped form,
NEWSEQUENTIALID()) keep new rows near each other. - Size matters. The key is repeated in every secondary index and every foreign key that points to it, so 16 bytes versus 36 characters adds up.
- They do not replace ordering. A version 4 UUID tells you nothing about creation time. Keep a
created_atcolumn. - Not secret by default. Time-based versions leak creation time, and a UUID is not an access token unless it comes from a secure random source.
- Compare in one form. Store lowercase hyphenated text or binary, not both;
'ABC...'and'abc...'are different strings. - When an integer is enough, a plain auto-increment key is smaller and faster. Use UUIDs when ids are created outside the database or must not be guessable in URLs.
- Validate the text form with a
CHECK(for examplelength(id) = 36) when the database has no native type.