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.

wanted_customer_id
foreign_key_join.sql
Replay: real traced execution (multi-file project)
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 (1)) 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 (2)) 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;

  1. tables ← 1 row

    1CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
    values this step2 rowstables

  3. customers ← 2 rows

    2CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);
    values this step2 rowscustomers

  4. orders ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);5WITH params(wanted_customer_id) AS (VALUES (1)) 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;
    values this step3 rowsorders

  5. result ← 2 rows

    4INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);5WITH params(wanted_customer_id) AS (VALUES (1)) 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;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);
    values this step1 rowtables

  2. tables ← 2 rows

    1CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);2CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');
    values this step2 rowstables

  3. customers ← 2 rows

    2CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(customer_id), total INTEGER);3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);
    values this step2 rowscustomers

  4. orders ← 3 rows

    3INSERT INTO customers VALUES (1, 'Ada'), (2, 'Lin');4INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);5WITH params(wanted_customer_id) AS (VALUES (2)) 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;
    values this step3 rowsorders

  5. result ← 1 row

    4INSERT INTO orders VALUES (101, 1, 30), (102, 1, 45), (201, 2, 20);5WITH params(wanted_customer_id) AS (VALUES (2)) 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;
    values this step1 rowresult
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.