A primary key uniquely identifies every row in a table. It combines uniqueness with a not-null requirement, preventing missing or duplicate row identifiers. section 2.3.1
Use primary keys to model identity in PostgreSQL tables:
- Define one column as the key, such as products.catalog.id or products.reviews.id. section 2.3.1
- Use UUID values when identifiers should not depend on numeric sequences; gen_random_uuid() can supply a default. section 2.4.2
- Use a compound primary key when uniqueness depends on several columns together. section 2.4.2
One product per order line
The sales.order_items table uses PRIMARY KEY (order_id, product_id). This lets one order contain different products while allowing each product to appear only once in that order. 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)
);
Inspect executable table definitions showing a UUID primary key on sales.orders and a compound primary key on sales.order_items. section 2.4.2
