Column Types, Constraints, and Relationships
What You'll Learn
You'll learn how to specify data types, primary keys, foreign keys, and define one-to-many relationships when creating SQLAlchemy models.
The Core Idea
When defining SQLAlchemy models, you're essentially describing your database schema in Python. Just like creating a table with CREATE TABLE in SQL, you need to specify what kind of data each column holds, which columns uniquely identify records, and how tables relate to each other.
Column Types map Python objects to SQL data types. SQLAlchemy provides types like Integer, String, DateTime, Boolean, and Float that translate to the appropriate database-specific types.
Constraints enforce rules on your data. The most important ones are:
primary_key=Truemarks a column as the unique identifier (like we covered in lesson 1048-1050)nullable=Falseprevents NULL valuesunique=Trueensures all values in that column are distinct
Relationships connect tables together. Using relationship() and ForeignKey, you define how records in one table relate to another—typically one-to-many (one author has many books) or many-to-one (many books belong to one author).
Think of it like designing a filing system: column types tell you what fits in each drawer (numbers, text, dates), constraints are the locks and rules (this drawer must always have content, these labels must be unique), and relationships are the reference cards that point from one filing cabinet to another.
When you define a foreign key with ForeignKey('other_table.id') and pair it with relationship(), SQLAlchemy handles the connection automatically, letting you navigate between related objects in Python without writing JOIN queries.
Key Takeaway: Column types, constraints, and relationships in SQLAlchemy models mirror SQL table definitions, allowing you to enforce data integrity and express table connections directly in Python code.