SQLAlchemy / Alembic wanting to drop & re-create index for foreign key

Question

total newbie to Alembic, SQLAlchemy, and Python. I've gotten to the point where Alembic is comparing existing objects in the database against the declarative classes I've made, and there's one pesky index (for a foreign key) that Alembic refuses to leave in-place in my initial migration.

I'm completely at a loss as to why the migration is continually trying to drop and re-create this index, which, if I leave in the migration I'll wager is going to fail anyway. Plus, if I don't reconcile the class to the database this will likely come up every time I auto-generate migrations.

Here's the pertinent part of what is in the upgrade method:

op.drop_index(
    'vndr_prod_tp_cat_category_fk_idx',
    table_name='vendor_product_types_magento_categories'
)

In the downgrade method:

op.create_index(  
    'vndr_prod_tp_cat_category_fk_idx',
    'vendor_product_types_magento_categories',
    ['magento_category_id'],
    unique=False
)

...here's the DDL for the table as it exists in MySQL:

CREATE TABLE `vendor_product_types_magento_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_product_type_id` bigint(20) unsigned NOT NULL,
  `magento_category_id` bigint(20) unsigned NOT NULL,
  `sequence` tinyint(3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vendor_product_types_magento_categories_uq` (`vendor_product_type_id`,`magento_category_id`,`sequence`),
  KEY `vndr_prod_tp_cat_category_fk_idx` (`magento_category_id`),
  CONSTRAINT `vndr_prod_tp_cat_magento_category_fk` FOREIGN KEY (`magento_category_id`) REFERENCES `magento_categories` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `vndr_prod_tp_cat_product_type_fk` FOREIGN KEY (`vendor_product_type_id`) REFERENCES `vendor_product_types` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8

...and here's the class I wrote:

from sqlalchemy import Column, Integer, UniqueConstraint, ForeignKeyConstraint, Index
from sqlalchemy.dialects.mysql import TIMESTAMP
from sqlalchemy.sql import text
from .base import Base


class VendorProductTypesMagentoCategories(Base):
    __tablename__ = 'vendor_product_types_magento_categories'

    id = Column(Integer, primary_key=True)
    vendor_product_type_id = Column(
        Integer,
        nullable=False
    )
    magento_category_id = Column(
        Integer,
        nullable=False
    )
    sequence = Column(Integer, nullable=False)
    created_at = Column(TIMESTAMP, server_default=text('CURRENT_TIMESTAMP'), nullable=False)
    updated_at = Column(
        TIMESTAMP,
        server_default=text('NULL ON UPDATE CURRENT_TIMESTAMP'),
        nullable=True
    )

    __table_args__ = (
        UniqueConstraint(
            'vendor_product_type_id',
            'magento_category_id',
            'sequence',
            name='vendor_product_types_magento_categories_uq'
        ),
        ForeignKeyConstraint(
            ('vendor_product_type_id',),
            ('vendor_product_types.id',),
            name='vndr_prod_tp_cat_product_type_fk'
        ),
        ForeignKeyConstraint(
            ('magento_category_id',),
            ('magento_categories.id',),
            name='vndr_prod_tp_cat_category_fk_idx'
        ),
    )

    def __repr__(self):
        return '<VendorProductTypesMagentoCategories (id={}, vendor_name={}, product_type={})>'.format(
            self.id,
            self.vendor_name,
            self.product_type
        )

Answer 1

You define your product foreign key in your python code as

    ForeignKeyConstraint(
        ('magento_category_id',),
        ('magento_categories.id',),
        name='vndr_prod_tp_cat_category_fk_idx'
    )

Here you use vndr_prod_tp_cat_category_fk_idx as the name of the foreign key constraint, not as the name of the underlying index, which explains why sqlalchemy wants to drop the index.

You should use vndr_prod_tp_cat_product_type_fk as the foreign key name and have a separate Index() construct with vndr_prod_tp_cat_category_fk_idx as name to create the index.