Setting Up Database Constraints with SQLAlchemy

Note: This post was originally published by Luis Natera on his personal blog. It has been republished here as part of TYN Studio's content.

Database constraints help maintain data integrity by enforcing rules at the database level. A common requirement is making a field unique within a specific context—for example, ensuring each user has unique post names, but different users can reuse the same names.

The Problem

You have a posts table where the post name should be unique per user. Multiple users should be able to create posts with the same name (like "My First Post"), but one user shouldn't have two posts with identical names.

The Wrong Approach

Your first instinct might be to use unique=True:

class Post(Base):
    __tablename__ = "post"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id"))
    name = Column(String, nullable=False, unique=True)

But this creates a global uniqueness constraint. Once one user creates a post named "My First Post", no other user can use that name. That's not what we want.

The Solution: Composite Unique Constraints

SQLAlchemy's UniqueConstraint lets you enforce uniqueness on a combination of columns:

from sqlalchemy import Column, Integer, String, DateTime, UniqueConstraint, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Post(Base):
    __tablename__ = "post"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id"))
    name = Column(String, nullable=False)

    __table_args__ = (
        UniqueConstraint('name', 'user_id', name='_name_user_uc'),
    )

How It Works

The __table_args__ attribute allows you to define table-level constraints. The UniqueConstraint takes:

1. The column names to include in the constraint
2. A name for the constraint (useful for migrations and error messages)

This creates a database-level constraint ensuring that the combination of name and user_id is unique. So:

• User 1 can create "My First Post"
• User 2 can also create "My First Post" (different user_id)
• User 1 cannot create another "My First Post" (same name + user_id combination)

Why Use Database Constraints?

You might wonder: couldn't I just check this in my application code?

# Don't do this as your only validation
existing = session.query(Post).filter_by(
    user_id=user_id,
    name=name
).first()

if existing:
    raise ValueError("You already have a post with this name")

While application-level validation is good, database constraints provide:

1. Race condition safety: Two simultaneous requests can't both pass validation
2. Database integrity: Constraint violations are impossible, even if bugs exist in application code
3. Cross-application consistency: If multiple applications access the database, constraints work for all
4. Clear error messages: Database constraint violations provide specific error messages

Multiple Constraints

You can add multiple constraints in __table_args__:

__table_args__ = (
    UniqueConstraint('name', 'user_id', name='_name_user_uc'),
    UniqueConstraint('slug', 'user_id', name='_slug_user_uc'),
    CheckConstraint('created_at <= updated_at', name='_created_before_updated'),
)

Resources

• SQLAlchemy: Constraints and Indexes
• SQLAlchemy: UniqueConstraint

Key Takeaway

When you need uniqueness within a context (per user, per organization, per category), use composite unique constraints with UniqueConstraint. This enforces your business rules at the database level, providing robust data integrity that survives application bugs, race conditions, and concurrent access.