Column types and constraints in Flask - Mini Project: Build & Apply

Column types and constraints

📖 Scenario: You are building a simple Flask app to manage a library's book collection. You need to create a database model to store book details with correct column types and constraints.

🎯 Goal: Create a Flask SQLAlchemy model called Book with columns that have specific types and constraints to store book information correctly.

📋 What You'll Learn

Create a Book model class inheriting from db.Model

Add a primary key column id with integer type

Add a title column with string type and maximum length 100, not nullable

Add an author column with string type and maximum length 50, not nullable

Add a published_year column with integer type

Add a isbn column with string type and maximum length 13, unique constraint

💡 Why This Matters

🌍 Real World

Defining database models with correct column types and constraints is essential for building reliable web applications that store and manage data accurately.

💼 Career

Understanding how to use Flask SQLAlchemy models with proper column types and constraints is a fundamental skill for backend web developers working with Python and Flask.

1

Set up the Book model with id column

Create a Flask SQLAlchemy model class called Book inheriting from db.Model. Add an integer column called id as the primary key.

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

# Create Book model with id column
# Your code here

Hint

Define a class named Book that inherits from db.Model. Use db.Column with db.Integer and primary_key=True for the id column.

2

Add title and author columns with constraints

Add a title column with string type, maximum length 100, and not nullable. Add an author column with string type, maximum length 50, and not nullable.

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # Add title and author columns below
    # Your code here

Hint

Use db.String with the length inside parentheses. Add nullable=False to make the columns required.

3

Add published_year column with integer type

Add a column called published_year with integer type to store the year the book was published.

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(50), nullable=False)
    # Add published_year column below
    # Your code here

Hint

Use db.Column(db.Integer) to define the published_year column.

4

Add isbn column with unique constraint

Add a column called isbn with string type, maximum length 13, and a unique constraint to ensure no duplicate ISBNs.

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Book(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100), nullable=False)
    author = db.Column(db.String(50), nullable=False)
    published_year = db.Column(db.Integer)
    # Add isbn column below
    # Your code here

Hint

Use db.String(13) and add unique=True to the isbn column.

Practice

1. Which SQLAlchemy column type should you use in Flask to store text data of variable length?

easy

A. db.String

B. db.Integer

C. db.Boolean

D. db.DateTime

Solution

1. Step 1: Understand column types for text data

   Text data of variable length is best stored using a string type that allows flexible length.

2. Step 2: Match the correct SQLAlchemy type

   db.String is designed for variable-length text, unlike db.Integer or db.Boolean.

3. Final Answer:

   db.String -> Option A

4. Quick Check:

   Variable-length text = db.String [OK]

Hint: Text data uses db.String type in Flask models [OK]

Common Mistakes:

• Choosing db.Integer for text data
• Using db.Boolean for strings
• Confusing db.DateTime with text types

2. Which of the following is the correct way to declare a non-nullable integer column named age in a Flask model?

easy

A. age = db.Column(db.Integer, nullable=True)

B. age = db.Column(db.Boolean, nullable=False)

C. age = db.Column(db.String, nullable=False)

D. age = db.Column(db.Integer, nullable=False)

Solution

1. Step 1: Identify the correct type for age

   Age should be stored as an integer, so db.Integer is correct.

2. Step 2: Set the nullable constraint

   To make the column non-nullable, use nullable=False.

3. Final Answer:

   age = db.Column(db.Integer, nullable=False) -> Option D

4. Quick Check:

   Integer + non-nullable = age = db.Column(db.Integer, nullable=False) [OK]

Hint: Use nullable=False to prevent empty values [OK]

Common Mistakes:

• Setting nullable=True when non-null is needed
• Using wrong data type like String for age
• Confusing Boolean with Integer type

3. Given this Flask model column declaration:

email = db.Column(db.String(120), unique=True, nullable=False)

What does the unique=True constraint do?

medium

A. Prevents duplicate email values in the table

B. Makes the email column optional

C. Allows multiple rows to have the same email

D. Automatically converts emails to lowercase

Solution

1. Step 1: Understand the unique constraint

   The unique=True constraint ensures no two rows have the same value in this column.

2. Step 2: Apply this to the email column

   This means duplicate emails are not allowed, keeping each email distinct.

3. Final Answer:

   Prevents duplicate email values in the table -> Option A

4. Quick Check:

   unique=True means no duplicates [OK]

Hint: unique=True means no two rows share the same value [OK]

Common Mistakes:

• Thinking unique=True allows duplicates
• Confusing unique with nullable
• Assuming unique changes data format

4. What is wrong with this Flask model column declaration?

price = db.Column(db.String, nullable=False, unique='yes')

medium

A. The nullable argument cannot be used with unique

B. The unique argument should be a boolean, not a string

C. The column type db.String cannot be used for price

D. The column name price is a reserved keyword

Solution

1. Step 1: Check the unique argument type

   The unique parameter expects a boolean value (True or False), not a string.

2. Step 2: Identify the error in the code

   Passing unique='yes' is invalid and will cause an error.

3. Final Answer:

   The unique argument should be a boolean, not a string -> Option B

4. Quick Check:

   unique must be True or False, not 'yes' [OK]

Hint: unique must be True/False, never a string [OK]

Common Mistakes:

• Using string values for boolean parameters
• Thinking nullable and unique conflict
• Assuming price is reserved keyword

5. You want to create a Flask model column for a user's phone number that must be exactly 10 digits, unique, and cannot be empty. Which declaration is best?

hard

A. phone = db.Column(db.String, unique=False, nullable=False)

B. phone = db.Column(db.Integer, unique=True, nullable=False)

C. phone = db.Column(db.String(10), unique=True, nullable=False)

D. phone = db.Column(db.String(15), nullable=True)

Solution

1. Step 1: Choose the correct type for phone numbers

   Phone numbers often contain leading zeros, so db.String(10) is better than db.Integer.

2. Step 2: Apply constraints for uniqueness and non-null

   Use unique=True to prevent duplicates and nullable=False to disallow empty values.

3. Final Answer:

   phone = db.Column(db.String(10), unique=True, nullable=False) -> Option C

4. Quick Check:

   String(10) + unique + non-null = phone = db.Column(db.String(10), unique=True, nullable=False) [OK]

Hint: Use String with length, unique=True, nullable=False for fixed phone numbers [OK]

Common Mistakes:

• Using Integer type losing leading zeros
• Allowing nullable=True when value is required
• Ignoring unique constraint for phone numbers