sqlalchemy/alembic · error · ValueError

No such constraint

Error message

No such constraint: '%s'

What it means

Raised as ValueError by ApplyBatchImpl.drop_constraint (batch.py:697) after a KeyError when a named constraint is not found in col_named_constraints, named_constraints, or unnamed_constraints, AND the constraint is not a type-bound constraint. The guard at line 691 silently ignores type-bound constraints (they live on the type object), but any other missing name is a real error because batch mode needs to drop it from the recreated table.

Solutions

  1. Verify the constraint exists: query the DB (e.g. SELECT conname FROM pg_constraint) and use the exact name.
  2. Check whether an earlier operation in the same batch or a prior revision already dropped it.
  3. If the drop is optional, guard it: first inspect via batch reflection or run a conditional drop only if the name resolves.

Example fix

// before
with op.batch_alter_table('users') as batch_op:
    batch_op.drop_constraint('users_email_key', type_='unique')  # wrong name
// after
with op.batch_alter_table('users') as batch_op:
    batch_op.drop_constraint('uq_users_email', type_='unique')  # actual name
Defensive patterns

Strategy: validation

Validate before calling

# Verify a constraint name exists on the table before dropping it in batch mode.
def constraint_exists(conn, table, name):
    return conn.exec_driver_sql(
        "SELECT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = %s)",
        (name,),
    ).scalar()

if constraint_exists(bind, 'users', 'uq_users_email'):
    with op.batch_alter_table('users') as batch_op:
        batch_op.drop_constraint('uq_users_email', type_='unique')

Type guard

def is_known_constraint(name: str, known: set[str]) -> bool:
    return name in known

Try / catch

from alembic.util import exc as alembic_exc

try:
    with op.batch_alter_table('users') as batch_op:
        batch_op.drop_constraint('maybe_missing', type_='unique')
except ValueError as e:
    if 'No such constraint' in str(e):
        # constraint already gone; treat as success for idempotent migrations
        pass
    else:
        raise

Prevention

When it happens

Trigger: batch_op.drop_constraint('does_not_exist', type_='unique') inside a batch_alter_table block where no constraint of that name exists on the table being recreated. Also when the constraint name was typo'd, lives on a different table, or was already dropped earlier in the same batch.

Common situations: Migration written against one branch's schema but applied to a DB at a different revision; constraint names that differ between environments (e.g. auto-generated names like users_email_key vs an explicit name); ordering two drop_constraint calls for the same name.

Related errors

AI-assisted analysis of sqlalchemy/alembic@5551b5d35f (2026-08-11). Data as JSON: /api/errors/c6e9e47ef4273c1d. Report an issue: GitHub.

Appendix: source

Thrown at alembic/operations/batch.py:697

            if const.name in self.col_named_constraints:
                col, const = self.col_named_constraints.pop(const.name)

                for col_const in list(self.columns[col.name].constraints):
                    if col_const.name == const.name:
                        self.columns[col.name].constraints.remove(col_const)
            elif constraint_name_string(const.name):
                const = self.named_constraints.pop(const.name)
            elif const in self.unnamed_constraints:
                self.unnamed_constraints.remove(const)

        except KeyError:
            if _is_type_bound(const):
                # type-bound constraints are only included in the new
                # table via their type object in any case, so ignore the
                # drop_constraint() that comes here via the
                # Operations.implementation_for(alter_column)
                return
            raise ValueError("No such constraint: '%s'" % const.name)
        else:
            if isinstance(const, PrimaryKeyConstraint):
                for col in const.columns:
                    self.columns[col.name].primary_key = False

    def create_index(self, idx: Index) -> None:
        self.new_indexes[idx.name] = idx  # type: ignore[index]

    def drop_index(self, idx: Index) -> None:
        try:
            del self.indexes[idx.name]  # type: ignore[arg-type]
        except KeyError:
            raise ValueError("No such index: '%s'" % idx.name)

    def rename_table(self, *arg, **kw):
        raise NotImplementedError("TODO")

View on GitHub (pinned to 5551b5d35f)