How to alter index of a column in alembic

Question

in version: 0001, I create a table users like below:

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('email', sa.String(length=255), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )

    op.create_index(op.f('ix_user_email'), 'users', ['email'], unique=True)

def downgrade():
    op.drop_table('users')

In version 0020, I want to alter the index of column email in users table. In this case, I want unique=False. Is there a way better than:

def upgrade():
    op.drop_index(op.f('ix_user_email'))
    op.create_index(op.f('ix_user_email'), 'users', ['email'], 
    unique=False)
def downgrade():
    op.drop_index(op.f('ix_user_email'))

Because of doing this, when downgrade to version 0019 column ['email'] don't have index at all.

So if there is a way to alter back and forth the index of column email. It's gonna be better.

There isn't a command to change an index in-place; you would either have to recreate the index after dropping it or emit the SQL for `ALTER INDEX ...`. — snakecharmerb, Jun 07 '22 at 08:21

Gord Thompson · Accepted Answer · 2022-06-07T12:46:19.787

1

You could restore the original index by adjusting the downgrade() method in 0020 to re-create it:

def downgrade():
    op.drop_index(op.f('ix_user_email'))
    # re-create previous version of index
    op.create_index(op.f('ix_user_email'), 'users', ['email'], unique=True)

edited Jun 07 '22 at 12:46

answered Jun 07 '22 at 12:24

Gord Thompson

116,920
32
215
418

How to alter index of a column in alembic

1 Answers1