MSSQL schema name is not passed to drop constraint statement

[ALMP-SallaH]

Hello, I'm having problem with dropping default constraint using alter_column operation.

Here is my setup:

• Microsoft SQL Server 2017-CU12-ubuntu
• pyodbc 4.0.26
• alembic 1.3.0
• sqlalchemy 1.3.11

I have the following table in my database

def upgrade():
    op.create_table('Client',
                    sa.Column('id', sa.Integer, primary_key=True),
                    sa.Column('name', sa.String(255), nullable=True),
                    schema='dm')

Suppose I have inserted few rows to table dm.Client

INSERT INTO dm.Client(name) values ('Sam'), ('John')

Now running the following upgrade fails

def upgrade():
    op.add_column(table_name='Client', column=sa.Column('client_id', sa.Integer, nullable=False, server_default='1'), schema='dm')
    op.alter_column(table_name='Client', column_name='client_id', schema='dm', server_default=None)

Error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'constraint'. (102) (SQLExecDirectW)")
[SQL: declare @const_name varchar(256)
select @const_name = [name] from sys.default_constraints
where parent_object_id = object_id('Client')
and col_name(parent_object_id, parent_column_id) = 'client_id'
exec('alter table [Client] drop constraint ' + @const_name)]
(Background on this error at: http://sqlalche.me/e/f405)

The problem is that schema 'dm' is not passed to statement and therefore constraint is not found. The following statement is correct and drops the constraint.

declare @const_name varchar(256)
select @const_name = [name] from sys.default_constraints
where parent_object_id = object_id('dm.Client')
and col_name(parent_object_id, parent_column_id) = 'client_id'
exec('alter table [dm].[Client] drop constraint ' + @const_name)

I'm not sure if this is an issue with Alembic or SQL Alchemy.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Support schemas for MSSQL drop server default + FK constraint https://gerrit.sqlalchemy.org/1578

[zzzeek]

hi -

can you please confirm latest commit 6e30795 resolves this for you? I can release then. thanks!

[ALMP-SallaH]

Works nicely, thank you very much.

[zzzeek]

it's released you should be GTG