How to define CheckConstraint properly for autogenerate?

[ziima]

With alembic 1.19.0 I discovered a number of remove_constraint operations detected in alembic check output. I looked around a bit and I discovered all are caused by the CheckConstraints defined as part of a column.

E.g. having model as

class Message(BaseModel):
    recipient: Mapped[str] = mapped_column("recipient", CheckConstraint("column_one IS NULL or column_other IS NULL", name="recipient"))

alembic check generates

('remove_constraint', CheckConstraint(<sqlalchemy.sql.elements.TextClause object at 0x7f7874eeee50>, name='message', table=Table('message', MetaData(), Column('x', Integer(), table=<message>), schema=None)))

The part Column('x', Integer() is a literal output, despite all my constraints are TEXT or UUID fields and none of the field is named x.

alembic check works fine, if I move the CheckConstraint to __table_args__ with no other changes.

Do I define the CheckConstraint incorrectly and am I required to define them only at __table_args__ or is that a bug in autogeneration?

[CaselIT]

Hi,

I think you may have found a bug in sqlalchemy or at the very least in the implementation of check constraint autogenerate in alembic:

import sqlalchemy as sa

t = sa.Table(
    "foo",
    sa.MetaData(),
    sa.Column("id", sa.Integer, sa.CheckConstraint("id > 0", name="bar")),
    sa.Column("name", sa.String(50)),
)

print(sa.schema.CreateTable(t))
print(t.constraints)

yelds:

CREATE TABLE foo (
        id INTEGER CONSTRAINT bar CHECK (id > 0),
        name VARCHAR(50)
)


{PrimaryKeyConstraint()}

@zzzeek do you remember the rationale for not registering the constraint on the table in sqlalchemy mike? CheckConstraint is a schema idem so its use in a column should be valid.

[CaselIT]

(created #1842 to track this)

[ziima]

I forgot to note that this affects also implicit CheckConstraints, such as those generated for booleans and enums on sqlite.

[CaselIT]

I think the surprising thing here is that the constraint isn't registered in the table, when both unique=True or index=True do:

Unique:

import sqlalchemy as sa

t = sa.Table(
    "foo",
    sa.MetaData(),
    sa.Column("id", sa.Integer, unique=True),
    sa.Column("name", sa.String(50)),
)

print(t.constraints)

{UniqueConstraint(Column('id', Integer(), table=<foo>)), PrimaryKeyConstraint()}

Index:

import sqlalchemy as sa

t = sa.Table(
    "foo",
    sa.MetaData(),
    sa.Column("id", sa.Integer, index=True),
    sa.Column("name", sa.String(50)),
)

print(t.indexes)

{Index('ix_foo_id', Column('id', Integer(), table=<foo>))}