Alembic keeps deleting and recreating Foreign Keys during autogenration

Question

After the autogenrated initial migration, when running another autogeneration all foreign keys are beeing dropped and recreated.

Example Code from the migration script:

op.drop_constraint('fk_foo_bar_id_bar', 'foo', type_='foreignkey')
op.create_foreign_key(op.f('fk_foo_bar_id_bar'), 'foo', 'bar', ['bar_id'], ['id'], source_schema='public', referent_schema='public')

I tried changing env.py include_object as well as the way migrations were run. I also tried changing both foo and bar model in any way that seemed reasonable. Naming the FKs also didnt work.

Answer 1

The solution lies in removing the explicit definition of the public schema in your classes.

So if you have this in your bar class

bar_id: Mapped[Integer] = mapped_column(
        ForeignKey("public.bar.id"), index=True, type_=Integer
    )

And in your foo class

__table_args__ = (
    {"schema": "public"},
)

Remove both public statements. Postgres does sometimes not explicitly name it that way and alembic then thinks it is a different FK and tries to recreate them.