Getting The object 'DF__tab_col_n__53BC6B19' is dependent on column 'col_name'.DB-Lib error when dropping column

[indiVar0508]

Hi,

I am trying to drop a column from mssql DB, which seems to have implicit default constraint created on it?

i have scenerio ( testing scenerio) where we are upgrading and downgrading DB for a performance usecase, so everytime DB upgrades it comes up with different constraint name, and when we downgrade i am getting this error.

E sqlalchemy.exc.OperationalError: (pymssql._pymssql.OperationalError) (5074, b"The object 'DF__table_nam__col_n__53BC6B19' is dependent on column 'col_name'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")
E   [SQL: ALTER TABLE table_name DROP COLUMN drop_column]

on looking into code i found that drop_column provides a flag for mssql_drop_default to drop default constraints but when i use it am getting this error,

E   sqlalchemy.exc.ProgrammingError: (pymssql._pymssql.ProgrammingError) (102, b"Incorrect syntax near 'constraint'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
E   [SQL: declare @const_name varchar(256)
E   select @const_name = [name] from sys.check_constraints
E   where parent_object_id = object_id('table_name')
E   and col_name(parent_object_id, parent_column_id) = 'col_name'
E   exec('alter table table_name drop constraint ' + @const_name)]
E   (Background on this error at: https://sqlalche.me/e/14/f405)

can you help me with resolution ?

I am using this MSSQL DB container

 mcr.microsoft.com/mssql/server:2017-latest

thanks!

[indiVar0508]

Figured in my the col was a FK and i was using drop_constraint explicitly for it and then also i applied mssql_drop_default so ig that's why issue happened, fixed it now it works

[indiVar0508]

Is it possible to have these flags in alter_column method when i alter a column i am gettng this error now

[CaselIT]

that's strange, i think mssql column default change is supported.

Can you provide the model/table and the migration commands you are trying that gives the error? Without it it's hard to suggest or guess what's happening

[CaselIT]

E   sqlalchemy.exc.ProgrammingError: (pymssql._pymssql.ProgrammingError) (102, b"Incorrect syntax near 'constraint'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
E   [SQL: declare @const_name varchar(256)
E   select @const_name = [name] from sys.check_constraints
E   where parent_object_id = object_id('table_name')
E   and col_name(parent_object_id, parent_column_id) = 'col_name'
E   exec('alter table table_name drop constraint ' + @const_name)]
E   (Background on this error at: https://sqlalche.me/e/14/f405)

this error suggest an issue in alembic, so maybe the "@const_name" part needs escaping?

alembic/alembic/ddl/mssql.py

Lines 298 to 320 in df26520

	def _exec_drop_col_constraint(
	element: _ExecDropConstraint, compiler: MSSQLCompiler, **kw
	) -> str:
	schema, tname, colname, type_ = (
	element.schema,
	element.tname,
	element.colname,
	element.type_,
	)
	# from http://www.mssqltips.com/sqlservertip/1425/\
	# working-with-default-constraints-in-sql-server/
	# TODO: needs table formatting, etc.
	return """declare @const_name varchar(256)
	select @const_name = [name] from %(type)s
	where parent_object_id = object_id('%(schema_dot)s%(tname)s')
	and col_name(parent_object_id, parent_column_id) = '%(colname)s'
	exec('alter table %(tname_quoted)s drop constraint ' + @const_name)""" % {
	"type": type_,
	"tname": tname,
	"colname": colname,
	"tname_quoted": format_table_name(compiler, tname, schema),
	"schema_dot": schema + "." if schema else "",
	}

there are also other similar looking queries in mssql that may have the same issue.

@indiVar0508 what is the table and contraint name?

@gordthompson do you know if there is a nicer way of composing that exec?

[indiVar0508]

@CaselIT This was older issue, i think i was dropping a foreign key column and passed mssql_drop_default=True but i was also explicitly dropping foreign key constraint i removed the flag for that column and it worked for me

[CaselIT]

sure, but it suggest there is an issue with that query that is issued by alembic.
I'm not familiar with mssql, but the error Incorrect syntax near 'constraint'.DB-Lib error message 20018 should mean that wrong sql was issued.

It would help know how the table / column / constraint are named to see if it's possible to reproduce the issue

[indiVar0508]

Sure @CaselIT ,

this is a MRC for issue, first generated a alembic migration with relationship defined then removed the relationship in second migration where i get this error once i upgrade

my upgrade method for below code is

def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # op.drop_constraint("fk_article_author_id", "author", type_="foreignkey")  # Fixes this issue

    op.drop_column("author", "article_id", mssql_drop_default=True)  # to fix it remove mssql_drop_default and uncomment above line

    # ### end Alembic commands ###

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Article(Base):
    __tablename__ = "article"
    id = sa.Column(sa.BigInteger, primary_key=True, autoincrement=True)
    name = sa.Column(sa.Unicode(255))
    content = sa.Column(sa.UnicodeText)


class Author(Base):
    __tablename__ = "author"
    id = sa.Column(sa.BigInteger, primary_key=True, autoincrement=True)
    name = sa.Column(sa.Unicode(255))

    # Generated First migration file with relationships defined,
    # Then removed this relationship so and in autogenerated revision file
    # used this op.drop_column("author", "article_id", mssql_drop_default=True)
    # Which gives
    # sqlalchemy.exc.ProgrammingError: (pymssql._pymssql.ProgrammingError) (102, b"Incorrect syntax near 'constraint'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")
    # [SQL: declare @const_name varchar(256)
    # select @const_name = [name] from sys.default_constraints
    # where parent_object_id = object_id('author')
    # and col_name(parent_object_id, parent_column_id) = 'article_id'
    # exec('alter table author drop constraint ' + @const_name)]
    # (Background on this error at: https://sqlalche.me/e/14/f405)

    # >>>  In second Alembic commit I removed this relationship <<<
    # article_id = sa.Column(sa.BigInteger, sa.ForeignKey("article.id"))
    # article = sa.relationship(Article)

[CaselIT]

thanks following up!

[CaselIT]

this error suggest an issue in alembic, so maybe the "@const_name" part needs escaping?

indeed it seems that QUOTENAME() is missing. I've created #1187