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?

It's not quite the same thing, but this might help: http://stackoverflow.com/questions/13756567/adding-primary-key-to-existing-mysql-table-in-alembic/13757758#13757758 — Rachel Sanders, Mar 13 '15 at 14:10
@RachelSanders I found that while searching for answers. Was hoping something's improved since 2012 though. Thanks for the response! — Eli, Mar 13 '15 at 18:16
@Eli It would be very useful the next time you find the answer to your own question if you were to post it below. On a quick google search for "alembic alter primary key" this post is the first result. I don't want to come across as a jerk but a question with an update from the original poster with "I found the answer" and no more details really does nothing for the community :-/. — Frito, May 02 '16 at 13:08
@Frito you misread my comments. I don't have a better answer than RachelSanders'. I'm still hoping a better method is added to Alembic in the future, but currently I have no better answer to post. — Eli, May 02 '16 at 18:29
@Eli Ha! You're indeed correct. I did misread your comment. My apologies. For anyone else reading the comments I ended up having an issue where the above error (150) was due to a FK constraint referencing the PK of the table I was trying to update. I ended up removing that constraint, updating my PK then re-adding the constraint. — Frito, May 03 '16 at 12:57
Update for 2023, as far as I can tell, alembic still hasn't added detection for primary key changes. So manually removing constraint is still the way to go. (I'd be happy to be proven wrong though) — Jonny Lin, Jul 11 '23 at 18:57

score 33 · Answer 1 · answered Jan 04 '18 at 08:02

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.

+1 This answer not only tells how to alter a primary key, but also how to do it when this primary key has a foreign key relationship with another table. — Shashank, May 24 '18 at 11:23
In my Postgres DB the PK constraints created by Alembic / SA are named using the `user_pkey` pattern instead of `pk_user` — dain, Feb 22 '19 at 18:02

score 18 · Answer 2 · edited May 23 '17 at 12:34

18

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!

edited May 23 '17 at 12:34

Community

1
1

answered Nov 22 '16 at 22:59

radtek

34,210
11
144
111

This was the solution for me (adding a new column to the composite primary key). The constraint — autogenerated by SQLAlchemy — was named `_pkey` in my case. – ebosi Oct 12 '20 at 18:09
1

Instead of the raw SQL `execute`, you could probably do something along these lines to stay with native alembic and SQLAlchemy: `op.add_column('similar_orders', sa.Column('id', sa.Integer, nullable=False, autoincrement=True, primary_key=True))` – alexanderdavide May 03 '21 at 14:57

score 0 · Answer 3 · answered Apr 11 '23 at 12:56

I am using sql script with (postgres sql):

def upgrade() -> None:
    op.execute("ALTER TABLE some_table ADD CONSTRAINT some_table_pkey PRIMARY KEY (col1, col2);")

def downgrade() -> None:
    op.execute("ALTER TABLE some_table DROP CONSTRAINT some_table_pkey;")

Alter Primary Key in Alembic?

3 Answers3

Linked