Alembic drop and create foreignkey on every migrate without any changes

[marcszy91]

Discussed in #1351

^{Originally posted by Ezak91 November 10, 2023}
We have a larger project with flask, sqlalchemy and flask-migrate (alembic).
We have completely recreated and migrated the database. If I perform another migrate immediately afterwards, it wants to delete all foreignkeys and create them again.

Before we used schema, it worked. But now we have to use schema and have the problem.

Database: MSSQL
alembic==1.11.1
Flask-Migrate==4.0.5
Flask-SQLAlchemy==2.5.1

It has multibe db (init --multidb)
and references to remote database on the same server in some relationship

Example:

Model

class CustomerAddress(db.Model):
    """Model for table customer_address"""

    __bind_key__ = "Customer"
    __tablename__ = "customer_address"
    __table_args__ = {"schema": "dbo"}

    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    customer_id = db.Column(db.ForeignKey("dbo.customer._id"))
    street = db.Column(db.String(255))
    house_number = db.Column(db.String(10))
    department = db.Column(db.String(50))
    zip_code = db.Column(db.String(10))
    city = db.Column(db.String(255))
    country = db.Column(db.String(255))

    customer = db.relationship("Customer", foreign_keys=[customer_id])

class Customer(db.Model):
    """customer table in Customer database"""

    __bind_key__ = "Customer"
    __tablename__ = "customer"
    __table_args__ = {"schema": "dbo"}

    _id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    description = db.Column(db.String(450), unique=True)
    company = db.Column(db.String(450))
    supplier_number = db.Column(db.String(450))
    sap_customer_number = db.Column(db.String(450))

    customer_address = db.relationship(
        "CustomerAddress",
        cascade="save-update, merge, delete, delete-orphan",
        overlaps="customer",
    )

Initial Migrate

def upgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('customer',
    sa.Column('_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('description', sa.String(length=450), nullable=True),
    sa.Column('company', sa.String(length=450), nullable=True),
    sa.Column('supplier_number', sa.String(length=450), nullable=True),
    sa.Column('sap_customer_number', sa.String(length=450), nullable=True),
    sa.PrimaryKeyConstraint('_id', name=op.f('pk_customer')),
    sa.UniqueConstraint('description'),
    schema='dbo'
    )
    op.create_table('customer_address',
    sa.Column('_id', sa.Integer(), autoincrement=True, nullable=False),
    sa.Column('customer_id', sa.Integer(), nullable=True),
    sa.Column('street', sa.String(length=255), nullable=True),
    sa.Column('house_number', sa.String(length=10), nullable=True),
    sa.Column('department', sa.String(length=50), nullable=True),
    sa.Column('zip_code', sa.String(length=10), nullable=True),
    sa.Column('city', sa.String(length=255), nullable=True),
    sa.Column('country', sa.String(length=255), nullable=True),
    sa.ForeignKeyConstraint(['customer_id'], ['dbo.customer._id'], name=op.f('fk_customer_address_customer_id_customer')),
    sa.PrimaryKeyConstraint('_id', name=op.f('pk_customer_address')),
    schema='dbo'
    )
    # ### end Alembic commands ###


def downgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('customer_address', schema='dbo')
    op.drop_table('customer', schema='dbo')
    # ### end Alembic commands ###

second migrate

INFO  [alembic.env] Migrating database Customer
INFO  [alembic.runtime.migration] Context impl MSSQLImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.autogenerate.compare] Detected removed foreign key (customer_id)(_id) on table customer_address
INFO  [alembic.autogenerate.compare] Detected added foreign key (customer_id)(_id) on table dbo.customer_address

def upgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('customer_address', schema=None) as batch_op:
        batch_op.drop_constraint('fk_customer_address_customer_id_customer', type_='foreignkey')
        batch_op.create_foreign_key(batch_op.f('fk_customer_address_customer_id_customer'), 'customer', ['customer_id'], ['_id'], referent_schema='dbo')

    # ### end Alembic commands ###


def downgrade_Customer():
    # ### commands auto generated by Alembic - please adjust! ###
    with op.batch_alter_table('customer_address', schema=None) as batch_op:
        batch_op.drop_constraint(batch_op.f('fk_customer_address_customer_id_customer'), type_='foreignkey')
        batch_op.create_foreign_key('fk_customer_address_customer_id_customer', 'customer', ['customer_id'], ['_id'])

Can anyone help?

Kind regards

[CaselIT]

duplicate of the discussion