Column types and constraints in Flask - Step-by-Step Execution

Concept Flow - Column types and constraints

Define Model Class

↓

Add Column with Type

↓

Add Constraints (nullable, unique, primary_key)

↓

Create Table in DB

↓

Use Model for CRUD Operations

This flow shows how to define a database model in Flask with columns, specify their types and constraints, then create the table and use it.

Execution Sample

Flask

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(120), unique=True, nullable=False)

Defines a User model with an integer primary key and a unique, non-nullable email string column.

Execution Table

Step	Action	Column	Type	Constraints	Result
1	Define id column	id	Integer	primary_key=True	Primary key column created
2	Define email column	email	String(120)	unique=True, nullable=False	Unique and required column created
3	Create table in DB	-	-	-	Table 'user' created with columns and constraints
4	Insert user with email	-	-	-	Success if email unique and not null
5	Insert user with duplicate email	-	-	-	Fails due to unique constraint
6	Insert user with null email	-	-	-	Fails due to nullable=False constraint
7	Query user by id	-	-	-	Returns user with matching id
8	End	-	-	-	Execution stops

💡 Execution stops after table creation and sample insertions demonstrate constraints enforcement.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
id column	undefined	Integer, primary_key=True	Integer, primary_key=True	Defined in DB table	Used as PK	Used as PK	Used as PK	Defined
email column	undefined	undefined	String(120), unique=True, nullable=False	Defined in DB table	Inserted unique email	Duplicate email error	Null email error	Defined

Key Moments - 3 Insights

Why does inserting a user with a duplicate email fail?

Because the email column has unique=True constraint, the database rejects duplicate values as shown in execution_table step 5.

What happens if we try to insert a user without an email?

The insert fails due to nullable=False constraint on the email column, preventing null values as shown in execution_table step 6.

Why is the id column special?

The id column is marked primary_key=True, so it uniquely identifies each row and is required, as shown in execution_table step 1.

Visual Quiz - 3 Questions

Look at the execution table, what constraint is applied to the email column at step 2?

A primary_key=True

B nullable=True

C unique=True, nullable=False

D default='[e-mail]'

💡 Hint

Check the Constraints column in execution_table row for step 2.

At which step does inserting a user with a null email fail?

A Step 4

B Step 6

C Step 5

D Step 7

💡 Hint

Look for the row mentioning null email error in execution_table.

If we remove unique=True from email, what changes in the execution?

A Duplicate emails would be allowed

B Emails would have to be unique

C Emails would become nullable

D Primary key would change

💡 Hint

Refer to the unique constraint effect shown in execution_table step 5.

Concept Snapshot

Define columns in Flask models using db.Column(type, constraints).
Common types: Integer, String(length).
Constraints: primary_key, unique, nullable.
Primary key uniquely identifies rows.
Unique prevents duplicates.
Nullable=False means value is required.

Full Transcript

In Flask, when defining database models, you create columns using db.Column and specify their data types like Integer or String. You can add constraints such as primary_key to mark a column as the unique identifier, unique to prevent duplicate values, and nullable to control if the column can be empty. For example, an id column with primary_key=True uniquely identifies each record. An email column with unique=True and nullable=False means every user must have a unique email address. When the table is created, these constraints are enforced by the database. Trying to insert duplicate or missing emails will cause errors. This visual trace shows how columns and constraints are defined, how the table is created, and how these rules affect inserting data.

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