Introduction

Sometimes a single column is not enough to define uniqueness. A database may allow the same value to appear many times on its own, while still requiring the combination of two or more columns to be unique, such as one slug per account or one seat assignment per event.

Declaring a Composite Unique Constraint

In SQLAlchemy, uniqueness across multiple columns is defined at the table level with UniqueConstraint. This is different from unique=True on a single column, which only protects that individual field.

Here is a runnable SQLAlchemy 2.x example that prevents the same email from joining the same team twice:

1from sqlalchemy import String, UniqueConstraint, create_engine
2from sqlalchemy.exc import IntegrityError
3from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
4
5class Base(DeclarativeBase):
6    pass
7
8class Membership(Base):
9    __tablename__ = "memberships"
10    __table_args__ = (
11        UniqueConstraint("user_email", "team_name", name="uq_membership_email_team"),
12    )
13
14    id: Mapped[int] = mapped_column(primary_key=True)
15    user_email: Mapped[str] = mapped_column(String(255), nullable=False)
16    team_name: Mapped[str] = mapped_column(String(100), nullable=False)
17
18engine = create_engine("sqlite+pysqlite:///:memory:", echo=False)
19Base.metadata.create_all(engine)
20
21with Session(engine) as session:
22    session.add_all(
23        [
24            Membership(user_email="[email protected]", team_name="platform"),
25            Membership(user_email="[email protected]", team_name="platform"),
26        ]
27    )
28
29    try:
30        session.commit()
31    except IntegrityError as exc:
32        session.rollback()
33        print("Duplicate blocked:", exc.__class__.__name__)

The database is the authority here. Even if two requests race each other, the constraint prevents invalid duplicate rows from being committed.

Why the Constraint Belongs in the Schema

It is tempting to check for duplicates in application code before inserting:

1from sqlalchemy import select
2
3with Session(engine) as session:
4    existing = session.scalar(
5        select(Membership).where(
6            Membership.user_email == "[email protected]",
7            Membership.team_name == "platform",
8        )
9    )
10
11    if existing is None:
12        session.add(Membership(user_email="[email protected]", team_name="platform"))
13        session.commit()

This pre-check can improve error messages, but it should never replace the database constraint. Two transactions can both pass the lookup and then race to insert. Only the unique constraint closes that gap.

That is why the usual production pattern is:

• Define UniqueConstraint in the model.
• Optionally perform a lookup for a friendly message.
• Still handle IntegrityError on commit.

Naming and Migrating the Constraint

Give the constraint an explicit name. That makes database errors clearer and helps migration tools generate predictable schema changes. In SQLAlchemy projects that use Alembic, the schema change belongs in a migration, not only in the Python model definition.

A concise model declaration is often enough:

1class Project(Base):
2    __tablename__ = "projects"
3    __table_args__ = (
4        UniqueConstraint("account_id", "slug", name="uq_project_account_slug"),
5    )
6
7    id: Mapped[int] = mapped_column(primary_key=True)
8    account_id: Mapped[int] = mapped_column(nullable=False)
9    slug: Mapped[str] = mapped_column(String(120), nullable=False)

With that schema, each account can reuse common slugs only if the account id is different. That pattern is very common in multi-tenant systems.

Common Pitfalls

• Using unique=True on both columns and expecting pairwise uniqueness. That would force each column to be globally unique, which is a different rule.
• Relying only on an application-level existence check. Race conditions still allow duplicates unless the database enforces the rule.
• Forgetting to handle IntegrityError. The constraint is working when the exception happens, so the application should roll back and respond cleanly.
• Leaving constrained columns nullable without understanding database behavior. Some databases treat multiple NULL values as distinct inside unique constraints.
• Updating the ORM model without creating the matching migration. The Python class alone does not change an existing production table.

Summary

• Use UniqueConstraint in __table_args__ when uniqueness depends on a combination of columns.
• Keep the constraint in the database schema, not only in application code.
• Catch IntegrityError and roll back the session when a duplicate insert is rejected.
• Give constraints explicit names so migrations and operational debugging are easier.
• Review nullability carefully, because unique constraint behavior around NULL can vary by database engine.