Schema Design Basics

Foreign Key Shape

Orders Belong to Customers

A foreign-key column stores the key of a row in another table. Joins use that shared key to combine related facts.

Program

Play the script to choose a customer id and list that customer's orders.

foreign_key_join.sql 
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);
WITH params(wanted_customer_id) AS (VALUES ()) SELECT customers.name, orders.order_id, orders.total FROM customers JOIN orders ON orders.customer_id = customers.customer_id WHERE customers.customer_id = (SELECT wanted_customer_id FROM params) ORDER BY orders.order_id;

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);
WITH params(wanted_customer_id) AS (VALUES ()) SELECT customers.name, orders.order_id, orders.total FROM customers JOIN orders ON orders.customer_id = customers.customer_id WHERE customers.customer_id = (SELECT wanted_customer_id FROM params) ORDER BY orders.order_id;
foreign key `orders.customer_id` stores a customer key on each order row.
REFERENCES `REFERENCES customers(customer_id)` documents the relationship in the schema.
join The join matches order rows to the customer row with the same key.