How can I add a foreign key constraint on an existing table column via SQLAlchemy?

Question

I'm using Flask, Alembic, and PostgreSQL with SQLAlchemy. I have an existing table location_messages with a column campaign_id. This was created initially in the model with the 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 the revision --autogenerate but it didn't create anything-- so I've been looking through the docs but I can't grok the syntax for my usage. For what it's worth, creating the table from scratch (after dropping it) in Alembic is

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 can't find a way to do this for an existing table. Is there a way to just get an instance of a table and assign a ForeignKeyConstraint to a column?

Answer 1

The Alembic op 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 constraint naming so that you can pass None as the name rather than naming it manually.

Answer 2

ForeignKey just need to be Tuple ... so instead of ['campaign_id'] write ('campaign_id',)

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