How to add multiple CheckConstraints?

Question

Migrated issue, originally created by Pekka Järvinen ()

Hi,

alembic is only generating the first CheckConstraint in the migrations file.

In __table_args__:

class User(Base):
    __tablename__ = 'users'
    __table_args__ = (
        CheckConstraint("login ~* '^[a-z]{3,}$'", name = __tablename__ + "_chk_login"),
        CheckConstraint("login != ''", name = __tablename__ + "_chk_login_not_empty"),
        CheckConstraint("password != ''", name = __tablename__ + "_chk_pw_not_empty"),
    )

    id = Column(Sequence(__tablename__ + "_id_seq"), primary_key = True, nullable = False)
    login = Column(Unicode(64), unique = True, nullable = False, default = "", index = True)
    password = Column(Unicode(255), nullable = False, default = "")

Outside of __table_args__

class User(Base):
    __tablename__ = 'users'

    id = Column(Sequence(__tablename__ + "_id_seq"), primary_key = True, nullable = False)
    login = Column(Unicode(64), unique = True, nullable = False, default = "", index = True)
    password = Column(Unicode(255), nullable = False, default = "")

    checks = Column(
        CheckConstraint("login ~* '^[a-z]{3,}$'", name = __tablename__ + "_chk_login"),
        CheckConstraint("login != ''", name = __tablename__ + "_chk_login_not_empty"),
        CheckConstraint("password != ''", name = __tablename__ + "_chk_pw_not_empty"),
    )

How to add multiple CheckConstraints? I couldn't find any documentation regarding this issue.

I'm using latest PostgreSQL, SQLAlchemy, Pyramid and alembic.

该提问来源于开源项目：sqlalchemy/alembic

Answer 1

Michael Bayer () wrote:

I put your code verbatim (using User1, User2) into an env.py file and did autogenerate against Postgresql:

"""rev1

Revision ID: c2adf65de4d6
Revises: 
Create Date: 2016-11-12 15:53:46.330554

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'c2adf65de4d6'
down_revision = None
branch_labels = None
depends_on = None

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.create_table('users1',
    sa.Column('id', sa.NullType(), nullable=False),
    sa.Column('login', sa.Unicode(length=64), nullable=False),
    sa.Column('password', sa.Unicode(length=255), nullable=False),
    sa.CheckConstraint(u"login != ''", name='users1_chk_login_not_empty'),
    sa.CheckConstraint(u"login ~* '^[a-z]{3,}$'", name='users1_chk_login'),
    sa.CheckConstraint(u"password != ''", name='users1_chk_pw_not_empty'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_users1_login'), 'users1', ['login'], unique=True)
    op.create_table('users2',
    sa.Column('id', sa.NullType(), nullable=False),
    sa.Column('login', sa.Unicode(length=64), nullable=False),
    sa.Column('password', sa.Unicode(length=255), nullable=False),
    sa.Column('checks', sa.NullType(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_users2_login'), 'users2', ['login'], unique=True)
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_users2_login'), table_name='users2')
    op.drop_table('users2')
    op.drop_index(op.f('ix_users1_login'), table_name='users1')
    op.drop_table('users1')
    ### end Alembic commands ###

column-level CHECK constraints are likely not autogenerated right now but table-level work fine and I cannot reproduce your issue.