On mssql, changing the nullable flag for a Boolean column will create another Check constraint

[sqlalchemy-bot]

Migrated issue, originally created by Sok Ann Yap (@sayap)

Here's a sample script:

#!python
import sqlalchemy as sa

from alembic import op


tests = sa.sql.table(
    'tests',
    sa.sql.column('col', sa.Boolean()),
)


def upgrade():
    op.add_column('tests', sa.Column('col', sa.Boolean()))

    op.execute(tests.update().values({'col': False}))

    op.alter_column('tests', 'col',
        type_=sa.Boolean(), nullable=False)


def downgrade():
    op.drop_column('tests', 'col', mssql_drop_check=True)

The downgrade function will fail, as the mssql_drop_check flag only expects one check constraint, but there are two of them now.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this seems like user error, "type_" means you want to change the type of the column to "boolean". If you want to change just nullability, you send the type along using "existing_type". "existing_type" is of course only needed because DB's like MSSQL and MySQL don't have proper ALTER commands.

However, I tried out "existing_type" and that seems to have a bug, in that it wants to drop the constraint. So the logic to drop/create constraints given types is refined in [[https://bitbucket.org/zzzeek/alembic/changeset/50c7551d280fdaa099f15427b1627940181594f8|50c7551d280fdaa099f15427b1627940181594f8]] to include that the constraint is dropped only if existing_type and type_ is given, and additionally no constraints are impacted at all on dialects that don't need these constraints such as postgresql.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed