Suppose customer details appear on every order row. Separating customers and orders reduces repeated data, but it creates a new question: which customer placed each order? A foreign key answers it. The order’s customer_id references the Customer table’s primary key, connecting the two tables without copying the customer record into every order. section 10.3.1 section 10.3.2
A primary key uniquely identifies a row in its own table. A foreign key holds a value that refers to a primary key in another table. An order can use more than one: customer_id connects it to a customer, while address_id connects it to an address. section 10.3.1 section 10.3.2
class Customer(db.Model): #1
__tablename__ = "customer" #2
customer_id = db.Column(db.Integer,
primary_key=True) #3
name = db.Column(db.String) #4
orders = db.relationship("Order",
backref=db.backref("customer")) #5
class Order(db.Model): #6
__tablename__ = "order" #7
order_id = db.Column(db.Integer,
primary_key=True) #8
customer_id = db.Column(db.Integer,
db.ForeignKey("customer.customer_id")) #9
address_id = db.Column(db.Integer,
db.ForeignKey("address.address_id")) #10
The SQLAlchemy models declare Order.customer_id and Order.address_id with db.ForeignKey(...). Customer.orders is different: it is a Python relationship attribute maintained by SQLAlchemy, not a column in the customer table. section 10.6.1
To retrieve related rows with SQL, JOIN specifies where a primary key equals a foreign key. A query can follow an order’s customer_id to its customer and its address_id to its address. Joining orders to items and products can then assemble invoice data from separate tables. section 10.3.4
SELECT c.name, a.street, a.zipcode, o.order_id, p.name, i.qty FROM 'order' o JOIN customer c ON c.customer_id = o.customer_id JOIN address a ON a.address_id = o.address_id JOIN item i ON o.order_id = i.order_id JOIN product p ON p.product_id = i.product_id
This invoice query follows foreign-key relationships across orders, customers, addresses, items, and products. Each ON condition names the matching columns. section 10.3.4
In Python, the same connection can be used through model relationships: customer.orders provides the associated Order instances, and an order’s customer attribute points back to its Customer. Foreign keys keep the stored records separate; joins and relationship attributes bring them together when needed. section 10.6.1 section 10.3.4
