UUID Primary Keys

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.

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