Alembic, How to alter a ForeigenKey Column

Question

I'm using Alembic 0.8.9, SQLAlchemy 1.1.4 and my database is a MySQL database.
I'm in the process of altering a table and a Foreign column:

In my database, I'm renaming 'organs' table to be named 'purposes'. Using

from alembic import op

def upgrade():
    op.rename_table('organs', 'purposes')

After that, I want to update my ForeignKey In a differnt table:

Before

class Order(DeclarativeBase):
    __tablename__ = 'orders'
    id = Column(Integer, autoincrement=True, primary_key=True)

    organ_id = Column(Integer, ForeignKey('organs.id'))

And After

class Order(DeclarativeBase):
    __tablename__ = 'orders'
    id = Column(Integer, autoincrement=True, primary_key=True)

    purpose_id = Column(Integer, ForeignKey('purposes.id'))

I need help writing an Alembic migrate script for this change to be reflected in the database. How do I alter a ForeignKey column?

Thanks for the help

Why "alter"? This seems more like "remove organ_id, add purpose_id". — sebastian, Jun 19 '17 at 12:56
If I drop and add, won't my data be lost? I'm also renaming the table 'organs' to 'purposes'. — A-Palgy, Jun 19 '17 at 12:58
That's right - I wasn't aware that you're actual goal is renaming of the foreign table. In that case my drop & create is indeed not sensible I guess — sebastian, Jun 19 '17 at 13:00
The MySQL [docs on the subject](https://dev.mysql.com/doc/refman/5.7/en/rename-table.html) are confusing. On the other hand there's "Foreign keys that point to the renamed table are not automatically updated. In such cases, you must drop and re-create the foreign keys in order for them to function properly.", but testing and [this bug report](https://bugs.mysql.com/bug.php?id=86030) seem to suggest otherwise. If the bug report proves true, you simply have to `op.alter_column('orders', 'organ_id', new_column_name='purpose_id')` after table rename. — Ilja Everilä, Jun 19 '17 at 13:39

score 17 · Accepted Answer · answered Jun 19 '17 at 14:13

Thanks for the helpful comments that led my to search a bit more about SQL Foreign Keys. I think I got it now.
This answer showed me the way:
How to change the foreign key referential action? (behavior)

Basically what I needed to do was rename the column (which holds the data), drop the old Foreign Key (constraint?) and create a new one instead.

Here is my migration script:

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.rename_table('organs', 'purposes')
    op.alter_column('orders', 'organ_id', new_column_name='purpose_id', existing_type=sa.Integer)
    op.drop_constraint(constraint_name="orders_ibfk_2", table_name="orders", type_="foreignkey")
    op.create_foreign_key(
        constraint_name="orders_ibfk_2",
        source_table="orders",
        referent_table="purposes",
        local_cols=["purpose_id"],
        remote_cols=["id"])


def downgrade():
    op.rename_table('purposes', 'organs')
    op.alter_column('orders', 'purpose_id', new_column_name='organ_id', existing_type=sa.Integer)
    op.drop_constraint(constraint_name="orders_ibfk_2", table_name="orders", type_="foreignkey")
    op.create_foreign_key(
        constraint_name="orders_ibfk_2",
        source_table="orders",
        referent_table="organs",
        local_cols=["organ_id"],
        remote_cols=["id"])

Alembic, How to alter a ForeigenKey Column

1 Answers1