CODEWITHSUNDEEP
pythonmysqlsqlalchemyalembicturbogears2
A-Palgy
A-Palgy

Reputation: 1429

Alembic, How to alter a ForeigenKey Column

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

Upvotes: 15

Views: 15571

Answers (1)

A-Palgy
A-Palgy

Reputation: 1429

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"])

Upvotes: 22

Related Questions