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