Fix the wrong foreign key on columns.column_name

[x4base]

In the commit c2bb49f, the foreign key that references datasource.datasource_name was set on columns.column_name when it should be columns.datasource_name. This causes errors when there are any new columns to be created when refreshing the druid datasources.

I am trying to generate a migration script to fix this. However, op.drop_constraint() requires the name of the foreign key you want to drop.
The following code works on SQLite because the foreign keys don't have any names:

naming_convention = {
    "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
}

def upgrade():
    with op.batch_alter_table('columns', schema=None, naming_convention=naming_convention) as batch_op:
        fk_name = 'fk_columns_column_name_datasources'
        batch_op.drop_constraint(fk_name, type_='foreignkey')
        batch_op.create_foreign_key(fk_name, 'datasources',
                                    ['datasource_name'], ['datasource_name'])

But for MySQL, the foreign keys are named like "columns_ibfk_1". Should I presume that on every machines, this foreign key has the same name and just drop the foreign key named columns_ibfk_1?
Do you have any suggestion about how to write a universal migration script that works on every SQL dialects? Or should I iterate through the known default names in different dialects in try-catch blocks?
I just couldn't figure out a way to drop the foreign key without knowing its name.

[sid88in]

For me https://github.com/airbnb/caravel/pull/531/files fixed the issue

[x4base]

Yes, because it is specialized for MySQL and will only work on MySQL. You can try it on SQLite or other databases. It won't work because the foreign key is not named that way

[x4base]

That's why I said I want a "universal migration script that works on every SQL dialects"

[waterwoodwind]

Yes，it can't get the real value from the foreign key on SQLite

[bkyryliuk]

class DruidColumn(Model, AuditMixinNullable):

    """ORM model for storing Druid datasource column metadata"""

    __tablename__ = 'columns'
    id = Column(Integer, primary_key=True)
    datasource_name = Column(
        String(255),
        ForeignKey('datasources.datasource_name'))
    # Setting enable_typechecks=False disables polymorphic inheritance.
    datasource = relationship('DruidDatasource', backref='columns',
                              enable_typechecks=False)

Models show correct foreign key now. Closing the issue.

[x4base]

Are you sure? It's not about the model, I know the model was fixed. The problem is whether the underlying database is migrated correctly.

[bkyryliuk]

@x4base - you right, looks like migrated DB doesn't have the foreign keys: