Intelligently ordering constraint/index drops

[WilliamDEdwards]

Is Alembic able to 'intelligently' order dropping constraints and indexes?

For example, I auto-generated the following migration:

op.drop_index(op.f('cluster_id'), table_name='clusters_borg_properties')
op.create_unique_constraint(op.f('uq_clusters_borg_properties_cluster_id'), 'clusters_borg_properties', ['cluster_id', 'not_deleted'])

... which fails because:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (1553, "Cannot drop index 'cluster_id': needed in a foreign key constraint")
[SQL: 
DROP INDEX cluster_id ON clusters_borg_properties]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

It would be a massive time-saver if Alembic understood the dependency of an index on a foreign key constraint that is also being manipulated.

---

Context:

I previously let the RDBMS generate constraint names, which was a big mistake as we now have inconsistent constraints amongst different environments, which makes schema migrations a nightmare.

Instead of taking the pragmatic route described by @zzzeek (#906 (reply in thread)), I would like to introduce a proper naming_convention once and for all, but preferably with the least amount of pain ;)

[zzzeek]

we have an issue for alembic to be smarter about self-referencing/cyclical foreign keys for tables at #326. but what you have here is many levels beyond that. SQLAlchemy has no notion of FK constraints being linked to indexes and this would be very complicated to implement all of these heuristics (whereas the database knows exactly the answer) , also it seems you're implying alembic would add additional "DROP" and "CREATE" of that foreign key surrounding those two operations (right?) . I dont know how to achieve any of that in a database agnostic way without tons of guessing or lots more rules and reflections packed into each dialect.

Since you are saying this would be a "massive-time-saver", that makes it seem like this otherwise unusual and infrequent operation where manual adjustment is hardly any inconvenience is instead something in your case that you're doing all the time, or at some large scale perhaps.

So in that case, what you should do right now, if you have a certain naming convention or pattern that you can recognize is use rewriter rules to 1. identify this sequence of operations and then 2. add the desired DropConstraint/AddConstraint operations for the FK that these depend on.

[WilliamDEdwards]

it seems you're implying alembic would add additional "DROP" and "CREATE" of that foreign key surrounding those two operations (right?)

Right.

Since you are saying this would be a "massive-time-saver", that makes it seem like this otherwise unusual and infrequent operation where manual adjustment is hardly any inconvenience is instead something in your case that you're doing all the time, or at some large scale perhaps.

It is (obviously) an incidental change, with a database consisting of 441 columns, 44 tables, and 223 constraints. Not the biggest schema, but painful to migrate nevertheless.

So in that case, what you should do right now, if you have a certain naming convention or pattern that you can recognize is use rewriter rules to 1. identify this sequence of operations and then 2. add the desired DropConstraint/AddConstraint operations for the FK that these depend on.

I'll look into that. Thanks for the hint!