How to remove a foreign key constraint in SQLAlchemy?

Question

I am using SQLAlchemy Migrate to track changes in the database and I am having trouble deleting the foreign key. I have two tables, t_new is a new table, and t_exists is an existing table. I need to add t_new and then add the foreign key to t_exists. Then I will need to cancel the operation (in which I am having problems).

t_new = sa.Table("new", meta.metadata, sa.Column("new_id", sa.types.Integer, primary_key=True) ) t_exists = sa.Table("exists", meta.metadata, sa.Column("exists_id", sa.types.Integer, primary_key=True), sa.Column( "new_id", sa.types.Integer, sa.ForeignKey("new.new_id", onupdate="CASCADE", ondelete="CASCADE"), nullable=False ) ) 

This works great:

 t_new.create() t_exists.c.new_id.create() 

But this is not so:

 t_exists.c.new_id.drop() t_new.drop() 

Attempting to reset the foreign key column gives an error: 1025, "Error renaming". \ my_db_name \ # sql-1b0_2e6 'to'. \ my_db_name \ exists' (errno: 150) "

If I do this using raw SQL, I can delete the foreign key manually and then delete the column, but I was not able to figure out how to remove the foreign key using SQLAlchemy? How to delete a foreign key and then a column?

Answer 1

You can do this with sqlalchemy.migrate.

To make it work, I had to explicitly create a foreign key constraint, and not implicitly with Column ('fk', ForeignKey ('fk_table.field')):

Alas, instead:

 p2 = Table('tablename', metadata, Column('id', Integer, primary_key=True), Column('fk', ForeignKey('fk_table.field')), mysql_engine='InnoDB', ) 

do the following:

 p2 = Table('tablename', metadata, Column('id', Integer, primary_key=True), Column('fk', Integer, index=True), mysql_engine='InnoDB', ) ForeignKeyConstraint(columns=[p2.c.fk], refcolumns=[p3.c.id]).create() 

Then the removal process is as follows:

 def downgrade(migrate_engine): # First drop the constraint ForeignKeyConstraint(columns=[p2.c.fk], refcolumns=[p3.c.id]).drop() # Then drop the table p2.drop() 

Answer 2

I managed to do this by creating a separate instance of metadata and using Session.execute () to run raw SQL. Ideally, there would be a solution using exclusively sqlalchemy, so I would not have to use MySQL-specific solutions. But at the moment I do not know such a solution.

Answer 3

I believe you can achieve this with SQLAlchemy-Migrate. Note that ForeignKey is in an isolated column. "ForeignKeyConstraint" is at the table level and links the columns together. If you look at the ForeignKey object in the column, you will see that it refers to ExternalKeyConstraint.

I would not be able to test this idea because the two databases that I use MS SQL are not supported by SqlAlchemy-Migrate, and sqlite does not support "alter table" for restrictions. I got SQLAlchemy to try to remove the FK using the restriction on restricting references to the sqlite table so that it looks good. YMMV.

Answer 4

Well, you can achieve this in sqlalchemy: just drop() all the constraints before you drop() column (theoretically, you can have several constraints):

 def drop_column(column): for fk in column.table.foreign_keys: if fk.column == column: print 'deleting fk ', fk fk.drop() column.drop() drop_column(t_exists.c.new_id)