Dropping foreign keys in Alembic downgrade?

Question

So I have created a number of tables in my database using an Alembic migration, each has an index and one or two foreign keys.

My upgrade method works fine, executes and creates the tables.

My downgrade method fails, after I drop my indexes and then drop my tables. I believe I also have to drop my foreign keys first? However I can't figure out how to drop foreign keys from the Alembic Documentation.

Downgrade method:

def downgrade():
    # Drop Indexes
    op.drop_index('ix_charge_id')
    op.drop_index('ix_statutory_provision_id')
    op.drop_index('ix_originating_authority_id')

    # Drop Tables
    op.drop_table('charge')
    op.drop_table('statutory_provision')
    op.drop_table('originating_authority')

Each of these three tables has a foreign key, so how do I go about dropping these first?

TYIA.

Answer 1

You just need to call drop constrain. So in your upgrade method you might have the following:

op.create_foreign_key(u'my_fkey', 'table1', 'table2', ['table2_id'], ['id'])

Then in your downgrade method you just need to have

op.drop_constraint(u'my_fkey', 'table1', type_='foreignkey')

One thing to watch for is that you assign a name when you create the foreign key, or known exactly the naming convention that will be used by the database. When you drop the constraint you need to specify the exact name being used.

One last thing which might help and save you time is to use the auto revision function of alembic. I find this saves a lot of the heavy lifting, and then I can just tweak the scripts if I need to.

alembic revision --autogenerate -m <message>

Check out http://alembic.zzzcomputing.com/en/latest/autogenerate.html for more information about the autogenerate, but it really is a time saver.