I am trying to add an 'id' primary key column to an already existing MySQL table using alembic. I tried the following...

op.add_column('mytable', sa.Column('id', sa.Integer(), nullable=False))
op.alter_column('mytable', 'id', autoincrement=True, existing_type=sa.Integer(), existing_server_default=False, existing_nullable=False) 

but got the following error

sqlalchemy.exc.OperationalError: (OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key') 'ALTER TABLE mytable CHANGE id id INTEGER NOT NULL AUTO_INCREMENT' ()

looks like the sql statement generated by alembic did not add PRIMARY KEY at the end of the alter statement. Could I have missed some settings?

Thanks in advance!


I spent some time digging through the alembic source code, and this doesn't seem to be supported. You can specify primary keys when creating a table, but not when adding columns. In fact, it specifically checks and won't let you (link to source):

# from alembic.operations.toimpl.add_column, line 132
for constraint in t.constraints:
    if not isinstance(constraint, sa_schema.PrimaryKeyConstraint):

I looked around, and adding a primary key to an existing table may result in unspecified behavior - primary keys aren't supposed to be null, so your engine may or may not create primary keys for existing rows. See this SO discussion for more info: Insert auto increment primary key to existing table

I'd just run the alter query directly, and create primary keys if you need to.


If you really need cross-engine compatibility, the big hammer would be to (1) create a new table identical to the old one with a primary key, (2) migrate all your data, (3)delete the old table and (4) rename the new table.

Hope that helps.

Your Answer

