A UUID is a standardized 128-bit identifier used for primary keys without depending on a database sequence. In a coffee-ordering system, Postgres can generate a random UUIDv4 when the application does not provide one. UUID-based keys avoid sequence dependencies and can scale better for distributed identifier generation. section 2.4.2
Listing 2.14 Creating orders and order_items tables
CREATE TABLE sales.orders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
customer_id INT REFERENCES customers.accounts(id),
order_date TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10, 2)
);
CREATE TABLE sales.order_items (
order_id UUID REFERENCES sales.orders(id),
product_id INT REFERENCES products.catalog(id),
quantity INT CHECK (quantity > 0),
price DECIMAL (10, 2),
PRIMARY KEY (order_id, product_id)
);
Use the table definition to see a UUID primary key generated by gen_random_uuid(), alongside order items that reference it. section 2.4.2
Recording an order
A transaction inserts an order with the UUID 19a0cffc-8757-453c-a4d2-b554fdc08954, inserts two related order items, decreases both stock quantities, and commits the changes atomically. section 2.4.2
Listing 2.15 Customer buying two products
BEGIN;
INSERT INTO sales.orders (id, customer_id, total_amount)
VALUES ('19a0cffc-8757-453c-a4d2-b554fdc08954', 1, 26.53);
INSERT INTO sales.order_items (order_id, product_id, quantity, price)
VALUES ('19a0cffc-8757-453c-a4d2-b554fdc08954', 1, 1, 16.54),
('19a0cffc-8757-453c-a4d2-b554fdc08954', 4, 1, 9.99);
UPDATE products.catalog
SET stock_quantity = stock_quantity - 1
WHERE id IN (1, 4);
COMMIT;
Run the complete order transaction to observe an application-supplied UUID used across the order and order_items records. section 2.4.2
