Connect an order to its product

Our product table describes what the shop sells. An order records a purchase. For this first example, each order contains exactly one product and one unit.

The order_id identifies an order. The product_id refers to a product in the other table. Two orders can refer to product 27 because two separate purchases can involve the same product.

The recorded paid_price is the amount charged for that purchase. We keep it even if the product’s current price changes. Otherwise a historical order could appear to cost more merely because the seller changed today’s price.

Follow one reference

To display order 501 with the product name, find order 501, read its product number, and find the product with that number.

Combining rows according to a condition is a join. Here the condition compares the two product-number columns:

SELECT orders.order_id, products.name, orders.paid_price
FROM orders
JOIN products
  ON orders.product_id = products.product_id
ORDER BY orders.order_id;

The dot in orders.product_id means “the product_id column from orders.” It removes ambiguity because both tables have a column with that name. ON supplies the matching condition. This inner join returns only matching pairs.

Make the relationship a rule

What if an order refers to product 99, but no such product exists? A foreign key can require every non-missing order product number to refer to a product that exists. It turns an expected relationship into a checked constraint.

A foreign key does not perform the join for us. The constraint checks whether the reference is permitted; the query asks the engine to combine facts. Deleting a referenced product also needs an explicit policy. Silently leaving a broken reference would defeat the rule.

Predict a repeated match

Product 27 has the current name “Canvas tote.” How many result rows will the query produce for orders 501 and 503? Which paid prices will they show?

We can now describe an order and its product without repeating every product fact in every purchase. But creating an order and reducing stock are two changes. Next we ask how to treat them as one successful purchase.

Further reading

PostgreSQL’s join tutorial explains how matching rows form results. Foreign-key constraints explain how an engine checks references.