Rectangle 27 0

sqlalchemy Adding primary key to existing MySQL table in alembic?


# from alembic.operations.add_column, line 284
for constraint in t.constraints:
  if not isinstance(constraint, schema.PrimaryKeyConstraint):
    self.impl.add_constraint(constraint)
op.execute("ALTER TABLE mytable ADD id INT PRIMARY KEY AUTO_INCREMENT;")

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 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:

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.

Note