<p dir="auto">When using mysql as URI scheme in caravel_config.py, everything works fine.<br>
I could add druid cluster. When I try to refresh druid metadata, it throws following error:</p>
<p dir="auto">sqlalchemy.exc.InvalidRequestError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (_mysql_exceptions.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (<code class="notranslate">caravel</code>.<code class="notranslate">columns</code>, CONSTRAINT <code class="notranslate">columns_ibfk_1</code> FOREIGN KEY (<code class="notranslate">column_name</code>) REFERENCES <code class="notranslate">datasources</code> (<code class="notranslate">datasource_name</code>))')</p>
<p dir="auto">On debugging, I figured out that while calling sync_to_db, DruidDatasource is trying to modify DruidColumn which I understand is not allowed.</p>
<p dir="auto">Could some one help me if I am doing something wrong or is it a valid issue.</p>
<p dir="auto">OS: Ubuntu 14.04<br>
Druid: 0.9<br>
Caravel: 0.9</p>
<p dir="auto">Steps to reproduce.</p>
<ol dir="auto">
<li>Change URI scheme to mysql as instructed in tutorial. Follow tutorial.</li>
<li>Add Druid cluster.</li>
<li>Do Druid Refresh Metadata.</li>
</ol>
<p dir="auto">In the commit <a class="commit-link" data-hovercard-type="commit" data-hovercard-url="https://github.com/apache/superset/commit/c2bb49fec567c72f9752f99db783225787d87eb6/hovercard" href="https://github.com/apache/superset/commit/c2bb49fec567c72f9752f99db783225787d87eb6"><tt>c2bb49f</tt></a>, 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.</p>
<p dir="auto">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.<br>
The following code works on SQLite because the foreign keys don't have any names:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="naming_convention = {
    &quot;fk&quot;: &quot;fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s&quot;,
}

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'])"><pre class="notranslate"><code class="notranslate">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'])
</code></pre></div>
<p dir="auto">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?<br>
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?<br>
I just couldn't figure out a way to drop the foreign key without knowing its name.</p>