How to add a foreign key constraint to an existing table column through SQLAlchemy?

Question

I use Flask, Alembic and PostgreSQL with SQLAlchemy. I have an existing location_messages table with a campaign_id column. At first it was created in a model with code

 campaign_id = db.Column(db.Integer) 

I want to add a foreign key to it, so I updated the model with

 campaign_id = db.Column(db.Integer, db.ForeignKey('campaigns.id')) 

I ran revision --autogenerate , but didn't create anything - so I was looking at docs , but I cannot understand the syntax for my use. For what it's worth, create a table from scratch (after deleting it) in Alembic

 op.create_table('location_messages', [...] sa.Column('campaign_id', sa.Integer(), nullable=True), sa.ForeignKeyConstraint(['campaign_id'], ['campaigns.id'], ), sa.PrimaryKeyConstraint('id') ) 

but for my life I cannot find a way to do this for an existing table. Is there a way to get an instance of a table and assign a ForeignKeyConstraint column?

Answer 1

The aerial search you are looking for is create_foreign_key .

 op.create_foreign_key( 'fk_location_message_campaign', 'location_messages', 'campaigns', ['campaign_id'], ['id'], ) 

It is recommended that you use automatic name restriction so that you can pass None as a name, rather than name it manually.