Column types and constraints in Flask

Introduction

Column types and constraints define what kind of data a database column can hold and rules it must follow. This helps keep data correct and organized.

When creating a new database table to store user information like names and emails.

When you want to make sure a column only accepts numbers or dates.

When you want to prevent duplicate entries in a column, like unique usernames.

When you want to make sure some columns always have a value (not empty).

When you want to link data between tables using keys.

Syntax

Flask

Column(column_type, constraint1, constraint2, ...)

Example:
name = Column(String(50), nullable=False, unique=True)

Column type defines the kind of data (e.g., String, Integer, Date).

Constraints are rules like nullable=False (must have value) or unique=True (no duplicates).

Examples

This creates an integer column named id that is the primary key (unique identifier).

Flask

id = Column(Integer, primary_key=True)

This creates a string column for emails that must be unique and cannot be empty.

Flask

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

This column stores date and time and must always have a value.

Flask

created_at = Column(DateTime, nullable=False)

Sample Program

This Flask app defines a User table with columns using types and constraints. It creates the table, adds one user, and prints the user info.

Flask

from flask_sqlalchemy import SQLAlchemy
from flask import Flask
from datetime import datetime

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///:memory:'
db = SQLAlchemy(app)

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(50), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    joined_on = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)

with app.app_context():
    db.create_all()
    user1 = User(username='alice', email='[e-mail]')
    db.session.add(user1)
    db.session.commit()
    print(f"User added: {user1.username}, Email: {user1.email}, Joined: {user1.joined_on}")

Important Notes

Use nullable=False to make sure a column always has data.

unique=True prevents duplicate values in a column.

Primary keys uniquely identify each row and are required for most tables.

Summary

Column types tell the database what kind of data to expect.

Constraints set rules to keep data valid and organized.

Using types and constraints helps avoid errors and keeps your data clean.