UUID as Primary Key: Pros, Cons, v4 vs v7

UUID vs auto-increment: quick answer

Should you use a UUID as primary key? Use a UUID when IDs are created in more than one place (several services, databases, devices or offline clients), when you merge data from different sources, or when IDs are visible in URLs and shouldn't reveal how many rows you have. Stick with an auto-increment integer for small, single-database tables where compact keys and sequential inserts matter more. If you choose UUIDs, prefer UUID v7: it's time-ordered, so it avoids most of the index fragmentation that random v4 keys cause.

Objective

Create tables using UUID (Universally Unique Identifier) as primary keys. UUIDs are ideal for distributed systems where sequential IDs could conflict across multiple sources.

UUID v4 vs v7 vs integer keys

• Auto-increment integers are compact and index-friendly, but they leak row counts, collide when you merge data from several databases, and need a central counter.
• UUID v4 (random) can be generated anywhere — app servers, devices, offline clients — with no coordination, at the cost of 16-byte keys and random index inserts.
• UUID v7 (time-ordered) keeps v4's generate-anywhere property but sorts by creation time, so new rows land at the end of the index like an integer would.

Use integers for small, single-database tables; use UUIDs when IDs are created in more than one place or exposed publicly.

Step 1: Create Table with UUID Primary Key

Create a table using UUID as the primary key.

CREATE TABLE documents (
    document_id UUID PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Records with UUID Values

Insert records with explicit UUID values.

INSERT INTO documents (document_id, title, content, author) VALUES
    ('550e8400-e29b-41d4-a716-446655440001', 'Getting Started Guide', 'Welcome to our platform...', 'Documentation Team'),
    ('550e8400-e29b-41d4-a716-446655440002', 'API Reference', 'Complete API documentation...', 'Engineering Team'),
    ('550e8400-e29b-41d4-a716-446655440003', 'Best Practices', 'Recommended approaches...', 'Architecture Team');

Step 3: Query by UUID

Query records using UUID values.

SELECT document_id, title, author
FROM documents
WHERE document_id = '550e8400-e29b-41d4-a716-446655440001';

Step 4: Create User System with UUIDs

Create a complete user system using UUIDs throughout.

CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_sessions (
    session_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    device_info VARCHAR(255),
    ip_address VARCHAR(45),
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

INSERT INTO users (user_id, email, username, display_name) VALUES
    ('6ba7b810-9dad-11d1-80b4-00c04fd430c8', 'alice@example.com', 'alice', 'Alice Johnson'),
    ('6ba7b811-9dad-11d1-80b4-00c04fd430c8', 'bob@example.com', 'bob', 'Bob Smith'),
    ('6ba7b812-9dad-11d1-80b4-00c04fd430c8', 'carol@example.com', 'carol', 'Carol Williams');

INSERT INTO user_sessions (session_id, user_id, device_info, ip_address) VALUES
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', '6ba7b810-9dad-11d1-80b4-00c04fd430c8', 'Chrome on Windows', '192.168.1.100'),
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a12', '6ba7b810-9dad-11d1-80b4-00c04fd430c8', 'Safari on iPhone', '192.168.1.101'),
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a13', '6ba7b811-9dad-11d1-80b4-00c04fd430c8', 'Firefox on Mac', '192.168.1.102');

Step 5: Create Order System with UUIDs

Create an order management system using UUID identifiers.

CREATE TABLE customers (
    customer_id UUID PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    contact_email VARCHAR(255) NOT NULL,
    contact_phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    customer_id UUID NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(12, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    item_id UUID PRIMARY KEY,
    order_id UUID NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- Insert sample data
INSERT INTO customers (customer_id, company_name, contact_email) VALUES
    ('f47ac10b-58cc-4372-a567-0e02b2c3d479', 'Acme Corporation', 'orders@acme.com'),
    ('f47ac10b-58cc-4372-a567-0e02b2c3d480', 'Tech Solutions Inc', 'purchasing@techsol.com');

INSERT INTO orders (order_id, customer_id, status, total_amount) VALUES
    ('7c9e6679-7425-40de-944b-e07fc1f90ae7', 'f47ac10b-58cc-4372-a567-0e02b2c3d479', 'completed', 1500.00),
    ('7c9e6679-7425-40de-944b-e07fc1f90ae8', 'f47ac10b-58cc-4372-a567-0e02b2c3d479', 'shipped', 750.00);

INSERT INTO order_items (item_id, order_id, product_name, quantity, unit_price) VALUES
    ('9a8b7c6d-5e4f-3a2b-1c0d-9e8f7a6b5c4d', '7c9e6679-7425-40de-944b-e07fc1f90ae7', 'Widget Pro', 10, 100.00),
    ('9a8b7c6d-5e4f-3a2b-1c0d-9e8f7a6b5c4e', '7c9e6679-7425-40de-944b-e07fc1f90ae7', 'Widget Basic', 20, 25.00);

Step 6: Join Tables with UUID Keys

Query across related tables using UUID foreign keys.

-- Get users with their active sessions
SELECT
    u.display_name,
    u.email,
    s.device_info,
    s.ip_address,
    s.started_at
FROM users u
INNER JOIN user_sessions s ON u.user_id = s.user_id
ORDER BY s.started_at DESC;

-- Get orders with customer information
SELECT
    c.company_name,
    o.order_id,
    o.status,
    o.total_amount,
    o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY o.order_date DESC;

Step 7: Count and Aggregate with UUIDs

Perform aggregations on UUID-keyed tables.

-- Count sessions per user
SELECT
    u.display_name,
    COUNT(s.session_id) as session_count
FROM users u
LEFT JOIN user_sessions s ON u.user_id = s.user_id
GROUP BY u.user_id, u.display_name;

-- Total orders per customer
SELECT
    c.company_name,
    COUNT(o.order_id) as order_count,
    SUM(o.total_amount) as total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.company_name;

Cleanup (Optional)

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS user_sessions;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS documents;

Expected Outcomes

• UUID primary keys uniquely identify records
• Foreign key relationships work with UUIDs
• JOINs function correctly with UUID keys
• No collision risk in distributed systems

UUID Format

Standard UUID format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

• 32 hexadecimal characters
• 4 hyphens as separators
• 128 bits total

Benefits of UUID Keys

Key Concepts Learned

• UUID data type for unique identifiers
• UUID as primary and foreign keys
• Distributed-friendly design
• Joining tables with UUID keys