Alter Primary Key in Alembic?

Question

I've read through the docs, but I can't find instructions on this anywhere. I tried dropping the old key and adding a new one, but that gets me errors:

op.drop_constraint('PRIMARY', 'some_table', type_='primary') op.create_primary_key('PRIMARY', 'some_table', ['col1', 'col2'])  sqlalchemy.exc.OperationalError: (OperationalError) (1025, "Error on rename of ... (errno: 150 - Foreign key constraint is incorrectly formed)") 'ALTER TABLE some_table DROP PRIMARY KEY ' () 

What am I doing wrong?

Answer 1

I also was in the same situation: alter primary key. In my case, I had to change the primary key type from integer to string.

The primary key also had a foreign key relationship to another table. The earlier alembic migration created the foreign key constraint in the following way:

#!/usr/bin/python3  from alembic import op import sqlalchemy as sa   def upgrade():     op.create_table('user',                     sa.Column('id', sa.Integer(), nullable=False),                     sa.Column('name', sa.String(length=100), nullable=False),                     sa.Column('username', sa.String(length=100), nullable=False),                     sa.PrimaryKeyConstraint('id', name=op.f('pk_user')),                     sa.UniqueConstraint('username', name=op.f('uq_user_username'))                     )      op.create_table('role',                     sa.Column('id', sa.Integer, primary_key=True),                     sa.Column('name', sa.String(100)),                     sa.Column('description', sa.String(255)),                     sa.PrimaryKeyConstraint('id', name=op.f('pk_role'))                     )      op.create_table('roles_users',                     sa.Column('user_id', sa.Integer, nullable=True),                     sa.Column('role_id', sa.Integer, nullable=True),                     sa.ForeignKeyConstraint(['user_id'], ['user.id'],                                             name=op.f('fk_roles_user_user_id_user')),                     sa.ForeignKeyConstraint(['role_id'], ['role.id'],                                             name=op.f('fk_roles_user_role_id_role'))                     ) 

Now when changing the primary key type of the user table from Integer to String, I had to do the following:

from alembic import op import sqlalchemy as sa   def upgrade():     # Drop primary key constraint. Note the CASCASE clause - this deletes the foreign key constraint.     op.execute('ALTER TABLE user DROP CONSTRAINT pk_user CASCADE')     # Change primary key type     op.alter_column('user', 'id', existing_type=sa.Integer, type_=sa.VARCHAR(length=25))     op.alter_column('roles_users', 'user_id', existing_type=sa.Integer, type_=sa.VARCHAR(length=25))     # Re-create the primary key constraint     op.create_primary_key('pk_user', 'user', ['id'])     # Re-create the foreign key constraint     op.create_foreign_key('fk_roles_user_user_id_user', 'roles_users', 'user', ['user_id'], ['id'], ondelete='CASCADE') 

Flask version: 0.12.1

Alembic version: 0.9.1

Python version: 3.4.4

Hope this information helps someone facing a similar problem.

Answer 2

I came across this question looking for a sample migration. So here is my full migration that drops the PK constraint and adds a new AUTO INCREMENT PK instead:

from alembic import op import sqlalchemy as sa from sqlalchemy.dialects.mysql import INTEGER   def upgrade():     op.drop_constraint('PRIMARY', 'similar_orders', type_='primary')      op.execute("ALTER TABLE similar_orders ADD COLUMN id INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT")   def downgrade():     op.drop_column('similar_orders', 'id')      op.create_primary_key("similar_orders_pk", "similar_orders", ["order_id", ]) 

Altering PK on column does not work in alembic, use drop_constraint instead, see here. Hope this helps!