Naming constraints with a metadata naming convention

Set naming_convention on your DeclarativeBase metadata before the first migration — ix, uq, ck, fk and pk templates — so every constraint gets the same name in every environment, and Alembic can drop and alter them by name. This guide belongs to managing enums, constraints and indexes in migrations.

Quick Answer

Without a convention, SQLAlchemy emits unnamed constraints and the database invents names. The model then has no way to refer to them, and Alembic cannot drop them.

Before — database-invented names, and a migration that cannot drop one:

from sqlalchemy import CheckConstraint, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    total_cents: Mapped[int]
    __table_args__ = (CheckConstraint("total_cents >= 0"),)

# PostgreSQL names them orders_pkey, orders_customer_id_fkey, orders_total_cents_check.
# A later autogenerated migration:
#     op.drop_constraint(None, "orders", type_="foreignkey")
# ValueError: Constraint must have a name

After — a naming convention on the metadata:

from sqlalchemy import CheckConstraint, ForeignKey, MetaData
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

NAMING_CONVENTION = {
    "ix": "ix_%(column_0_label)s",
    "uq": "uq_%(table_name)s_%(column_0_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}


class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)                  # pk_orders
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id"))
    # fk_orders_customer_id_customers
    total_cents: Mapped[int]
    __table_args__ = (
        CheckConstraint("total_cents >= 0", name="positive_total"),   # ck_orders_positive_total
    )

The check constraint still needs a short name=, because the ck template uses %(constraint_name)s — there is no column to name a check after. Omit it and SQLAlchemy raises InvalidRequestError: Naming convention including %(constraint_name)s token requires that constraint is explicitly named.

Execution Context & Async Workflow Integration

A naming convention is applied when SQLAlchemy builds a constraint object attached to a table in that MetaData. It fills in the template from the table and columns, and the resulting name is used in every piece of DDL the metadata produces: create_all(), Alembic's autogenerate output, and the comparison autogenerate performs against the database.

That last point is what makes conventions matter for migrations. Autogenerate matches constraints by name. When the model has a named constraint and the database has the same name, it knows they are the same object and compares their definitions. When the model's constraint is unnamed, autogenerate can still emit op.create_foreign_key(None, ...) — PostgreSQL will invent a name — but it cannot emit a working drop, because a drop needs a name, and so it writes op.drop_constraint(None, ...), which fails when the migration runs.

Invented names are also not portable. PostgreSQL produces orders_customer_id_fkey, MySQL produces orders_ibfk_1, and SQLite keeps most constraints anonymous. A migration hand-written against a developer's PostgreSQL works in production only if production invented the same name, which is true until the day a table was created differently.

Alembic wraps names produced from the convention in a marker, op.f("fk_orders_customer_id_customers"), in generated scripts. op.f() says "this name is already final"; without it, a name passed to an operation is treated as the %(constraint_name)s input and run through the convention again, producing ck_orders_ck_orders_positive_total. Leave op.f() in place when editing generated revisions.

The convention costs nothing at runtime and is invisible to the async engine — it affects only DDL, and Alembic's async env.py runs autogenerate inside run_sync() against the same target_metadata. The only requirement is that env.py imports the Base.metadata that carries the convention, not a bare MetaData() created elsewhere, which is a quiet way to lose it — as described in setting up Alembic env.py for asyncpg.

Resolving Warnings, Errors & Common Mistakes

The drop-and-recreate row is the one that catches teams adopting a convention late. After adding naming_convention, the model's foreign key is called fk_orders_customer_id_customers while the database's is still orders_customer_id_fkey. Autogenerate sees two different constraints: one only in the database, to drop, and one only in the model, to create. Applied as generated, that drops and re-adds every foreign key — which on large tables means re-validating every row under lock. The next section renames instead.

Advanced: Adopting a Convention on an Existing Database

Renaming constraints is cheap — ALTER TABLE ... RENAME CONSTRAINT and ALTER INDEX ... RENAME TO only change catalog entries — so the adoption migration is fast even on large tables. The work is producing the list of renames, and the inspector does it by comparing reflected names with the names the convention would produce.

import asyncio

from sqlalchemy import inspect
from sqlalchemy.ext.asyncio import create_async_engine

from shop.models import Base


def _planned_renames(sync_conn) -> list[str]:
    inspector = inspect(sync_conn)
    statements: list[str] = []
    for table in Base.metadata.sorted_tables:
        live_fks = {tuple(fk["constrained_columns"]): fk["name"]
                    for fk in inspector.get_foreign_keys(table.name)}
        for fk in table.foreign_key_constraints:
            cols = tuple(c.name for c in fk.columns)
            live = live_fks.get(cols)
            if live and live != fk.name:
                statements.append(
                    f'ALTER TABLE {table.name} RENAME CONSTRAINT "{live}" TO "{fk.name}"'
                )
        live_uqs = {tuple(uq["column_names"]): uq["name"]
                    for uq in inspector.get_unique_constraints(table.name)}
        for uq in table.constraints:
            if uq.__class__.__name__ == "UniqueConstraint":
                cols = tuple(c.name for c in uq.columns)
                live = live_uqs.get(cols)
                if live and live != uq.name:
                    statements.append(
                        f'ALTER TABLE {table.name} RENAME CONSTRAINT "{live}" TO "{uq.name}"'
                    )
        pk = inspector.get_pk_constraint(table.name)
        if pk["name"] and pk["name"] != table.primary_key.name:
            statements.append(
                f'ALTER TABLE {table.name} RENAME CONSTRAINT "{pk["name"]}" '
                f'TO "{table.primary_key.name}"'
            )
        live_ix = {tuple(ix["column_names"]): ix["name"]
                   for ix in inspector.get_indexes(table.name)}
        for index in table.indexes:
            cols = tuple(c.name for c in index.columns)
            live = live_ix.get(cols)
            if live and live != index.name:
                statements.append(f'ALTER INDEX "{live}" RENAME TO "{index.name}"')
    return statements


async def main() -> None:
    engine = create_async_engine("postgresql+asyncpg://shop:secret@db/shop")
    async with engine.connect() as conn:
        for statement in await conn.run_sync(_planned_renames):
            print(f'    op.execute(\'{statement}\')')
    await engine.dispose()


asyncio.run(main())

Paste the printed lines into a new revision's upgrade(), reverse them for downgrade(), and apply it. Check constraints are left out of the script deliberately: the inspector reports their SQL text in the database's normalised form, which rarely matches the model's text, so matching them automatically is unreliable. List them with inspector.get_check_constraints() and rename by hand.

The finishing test is the one that matters: run alembic revision --autogenerate against the renamed database. An empty upgrade() means the model and the database agree on every name, and from here on every generated migration will too. Checking that in CI, as described in detecting column type and server default changes in autogenerate, keeps it that way.

Multi-Column Constraints and Custom Tokens

The templates in the quick answer name a constraint after its first column, which is fine until a table has two composite constraints that start with the same column. UniqueConstraint("tenant_id", "sku") and UniqueConstraint("tenant_id", "slug") both become uq_products_tenant_id, and the second CREATE fails with relation "uq_products_tenant_id" already exists.

SQLAlchemy provides multi-column variants of each column token. %(column_0_N_name)s joins every column name with underscores, and %(column_0N_name)s joins them with no separator:

from sqlalchemy import MetaData, UniqueConstraint
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

NAMING_CONVENTION = {
    "ix": "ix_%(table_name)s_%(column_0_N_name)s",
    "uq": "uq_%(table_name)s_%(column_0_N_name)s",
    "ck": "ck_%(table_name)s_%(constraint_name)s",
    "fk": "fk_%(table_name)s_%(column_0_N_name)s_%(referred_table_name)s",
    "pk": "pk_%(table_name)s",
}


class Base(DeclarativeBase):
    metadata = MetaData(naming_convention=NAMING_CONVENTION)


class Product(Base):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(primary_key=True)
    tenant_id: Mapped[int]
    sku: Mapped[str]
    slug: Mapped[str]
    __table_args__ = (
        UniqueConstraint("tenant_id", "sku"),    # uq_products_tenant_id_sku
        UniqueConstraint("tenant_id", "slug"),   # uq_products_tenant_id_slug
    )

Choose the multi-column form from the start if you can. Switching templates later renames every existing constraint in the model, and the adoption script from the previous section has to be run again.

When no built-in token fits, a convention can reference a token you define as a function. The function receives the constraint and its table and returns a string, which is useful for shortening predictably long names yourself rather than relying on truncation:

import hashlib

from sqlalchemy import MetaData


def column_hash(constraint, table) -> str:
    joined = "_".join(column.name for column in constraint.columns)
    return hashlib.sha1(joined.encode()).hexdigest()[:8]


metadata = MetaData(
    naming_convention={
        "column_hash": column_hash,
        "ix": "ix_%(table_name)s_%(column_hash)s",
    }
)

A hashed name is stable and short but tells a reader nothing, so reserve it for index names on tables with many wide composite indexes, and keep readable names for constraints that appear in error messages. IntegrityError output quotes the constraint name, and uq_products_tenant_id_sku in an alert explains itself in a way ix_products_3f9a1c2d never will.

Frequently Asked Questions

Related

• Managing Enums, Constraints and Indexes in Migrations — The parent guide: schema objects that need hand-written migrations.
• Creating indexes concurrently in Alembic migrations — Promoting a concurrently built index to a named unique constraint.
• Adding a value to a Postgres enum in Alembic — Named CHECK constraints as an alternative to native enums.
• Autogenerating and Reviewing Migration Scripts — What autogenerate compares, and what to check before merging.