[SOLVED] SQLAlchemy migrations generating foreign keys with different names. How to drop these?

SQLAlchemy migrations generating foreign keys with different names. How to drop these?

In my SQLAlchemy data model I had a reference from project->customer. I am doing migrations and initially this FK was created via

sa.ForeignKeyConstraint(['customer_id'], ['customers.id'], )

(this was during the same migration that created the project table so the auto generated down is to simply drop_table).

Now I am removing this reference and hence dropping that constraint. The auto-generated migration for it is

op.drop_constraint('FK__projects__custom__412EB0B6', 'projects', type_='foreignkey')

The problem is that the constraint isn't always named that. In one database I checked its named FK__projects__custom__2E1BDC42, in another yet another thing...How do I drop the constraint properly and whats causing the difference in names?

Edit: Apparently I had the option to name the constraint ... which the docs don't of course mention being a good and necessary idea. So...I know how to prevent this in the future, but not how to fix the current issue.

Solution

I ended up adding this to my migration

op.execute("""
    DECLARE @fk_project_customer varchar(50);
    SELECT @fk_project_customer = (SELECT name FROM sys.foreign_keys WHERE name LIKE 'FK__projects__custom__%');
    EXEC('ALTER TABLE projects DROP CONSTRAINT "' + @fk_project_customer + '"');
""")

So it basically finds the constraint name that follows this pattern from sys.foreign_keys as @Ilja recommended, then EXEC a dynamic sql query from it