Alembic drops and creates foreign key constraints (autogenerate) when the table has different schema than the referenced columns

Viewed 65

I am trying to use the autogenerate feature from alembic to update the schema of a MSSQL database.

class Table1(Base):
    __tablename__ = "table1"
    __table_args__ = (
        PrimaryKeyConstraint("ID", name="PK_Table1"),
    )

    ID = Column(Integer, nullable=False)

class Table2(Base):
    __tablename__ = "table2"
    __table_args__ = (
        ForeignKeyConstraint(['Table1ID'], ['Table1.ID'], name='fk_Table2_Table1')
        {'schema': 'foo'}
    )

    Table1ID = Column(Integer, nullable=False)
    Table1_ = relationship('Table1', back_populates='Table2')

After executing the command alembic revision --autogenerate, this is the upgrade() function I get:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_Table2_Table1', 'Table2', schema='foo', type_='foreignkey')
    op.create_foreign_key('fk_Table2_Table1', 'Table2', 'Table1', ['Table1ID'], ['ID'], source_schema='foo')
    # ### end Alembic commands ###

After digging into the code, I found that when alembic compares the foreign keys between the schema and the model in the code, sqlalchemy forces a dbo schema to the Table1.ID referenced column:

Database foreign key: ('foo', 'Table2', ('Table1ID',), 'dbo', 'Table1', ('ID',), None, None, 'not deferrable')

Model foreign key: ('foo', 'Table2', ('Table1ID',), None, 'Table1', ('ID',), None, None, 'not deferrable')

This difference leads to the drop and create commands in the upgrade() function later on. If I remove the {'schema': 'foo'} in __table_args__ the issue disappears so my guess is that the table schema (different from the default one) forces the schema on the foreign key reference.

I would like to know if there is any way to overcome this problem.

0 Answers
Related