Search code examples
sql-serversqlalchemyconstraintsalembic

Constraint name for default value for sql server with alembic

My alembic migration adds a column to a table like this:

op.add_column("OrderTable", sa.Column("item_counter", sa.Integer(), nullable=False, server_default="0"))

When I run the migration, SQL Server generates a DF_Order_item_cou__39209daf constraint because of the server_default which I must use to set the item_counter to 0 by default in a non-nullable column.

The problem is that running an alembic downgrade -1 fails because the constraint was generated by SQL Server and I don't seem to have control over constraint names that are created for default values. I thought I could add to the naming_conventions dict that is in my MetaData in the Base model like so:

class Base(DeclarativeBase):
  metadata = MetaData(naming_convention: {
   "df": "df_%(table_name)s_%(column_0_name)s", ...
}

But these DF constraints seem particular to SQL Server and not sure how I can gain control over their names when using alembic and SQLAlchemy as described in Alembic: The Importance of Naming Constraints

Solution

  • Turns out Alembic has a few special switches in the drop_column for dealing with SqlServer. Read about them here What I needed was:

    op.drop_column("OrderTable", "item_counter", mssql_drop_default=True)