render inline CHECK constraint for ADD COLUMN on sqlite

[sqlalchemy-bot]

Migrated issue, originally created by Márcio Moreira (@arqeco)

Friends,

I´ve tried without success to add a boolean column to a sqlite file.

File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 331, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) near "CHECK": syntax error u'ALTER TABLE movimentos ADD CHECK (abc IN (0, 1))' ()

The only way it worked was changing

def upgrade():
    op.add_column('movimentos', sa.Column('abc', sa.Boolean))

to

def upgrade():
    op.add_column('movimentos', sa.Column('abc', sa.Boolean(create_constraint=False) ))

I am new to sqlalchemy and databases in general. Is it the create_constraint=False mandatory for Sqlite databases in alembic?

Greetings,
Márcio

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

I'd say you've had success as just using that flag allows it to skip that part of SQLite ALTER which is not supported. Until 0.4.2 comes out that is the workaround.

SQLite has very little support for ALTER and schema migrations. In this case, SQLAlchemy itself doesn't support the column-inline CHECK syntax SQLite is requiring, so for now the CHECK constraint is skipped in b3bc2d8.

This ticket needs to stay open because theoretically we can support the addition of the CHECK constraint as well but SQLAlchemy itself would need some changes to handle this.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed title from "Unable to add boolean column" to "render inline CHECK constraint for ADD COLUMN on s"

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: external SQLAlchemy issues

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

this should be handled by batch migrations

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed