Add autoincrement column that is not primary key Alembic

Question

I want to add a column that is autoincrement that is not primary key to an existing MySQL database.

The command issued on the server required for this operation is the following:

ALTER TABLE `mytable` ADD `id` INT UNIQUE NOT NULL AUTO_INCREMENT FIRST

The issue I face is how to replicate this table change through an Alembic migration. I have tried:

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('mytable', sa.Colummn('id', sa.INTEGER(), 
                  nullable=False, autoincrement=True)

but when I try to insert a row with the following command:

INSERT INTO `mytable` (`col1`, `col2`) VALUES (`bar`);

where col1, col2 are non nullable columns. When I insert this record I expect the table to generate automatically the id for me.

ERROR 1364 (HY000): Field 'id' doesn't have a default value

If I inspect the sql autogenerated by Alembic using the following command:

alembic upgrade 'hash-of-revision' --sql

it spits out, for the given revision:

ALTER TABLE mytable ADD COLUMN id INTEGER NOT NULL;

Which means that either Alembic or SQLAlchemy is ignoring the autoincrement field when generating the sql of the migration.

Is there a way I can solve this? Or can I establish a migration based on a custom sql command?

Answer 1

First we add the column and allow it to have null values

op.add_column('abc', Column('id', BIGINT(unsigned=True), comment='This column stores the type of phrase') )

Then we create the primary key

op.create_primary_key( 'abc_pk', table_name='abc', columns=['id'] )

Not sure how it allows me to add a primary key on an empty column but i guess it is because it is in a transaction block.

Then we alter the column to have the autoincrement column

op.alter_column( existing_type=BIGINT(unsigned=True), table_name='abc', column_name='id', autoincrement=True, existing_autoincrement=True, nullable=False)