How do I delete a foreign key constraint in SQLAlchemy?

Question

I'm using SQLAlchemy Migrate to keep track of database changes and I'm running into an issue with removing a 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, then add a foreign key to t_exists. Then I need to be able to reverse the operation (which is where I'm having trouble).

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 fine:

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

But this does not:

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

Trying to drop the foreign key column gives an error: 1025, "Error on rename of '.\my_db_name\#sql-1b0_2e6' to '.\my_db_name\exists' (errno: 150)"

If I do this with raw SQL, i can remove the foreign key manually then remove the column, but I haven't been able to figure out how to remove the foreign key with SQLAlchemy? How can I remove the foreign key, and then the column?

prinzdezibel · Accepted Answer · 2011-03-29T15:47:45.433

You can do it with sqlalchemy.migrate.

In order to make it work, I have had to create the foreign key constraint explicitly rather than implicitely with Column('fk', ForeignKey('fk_table.field')):

Alas, instead of doing this:

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

do that:

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 deletion process looks like this:

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

For anyone who may run into this - ForeignKeyConstraint needs to be imported from `migrate` not from `sqlalchemy`. — mjallday, Sep 27 '12 at 22:49
I don't know in what version of sqlalchemy de `drop` method was removed, but i managed to achieve the same using `op.drop_constraint(_FK_NAME, _TABLE_NAME, type_="foreignkey")`, and creating by using: `op.create_foreign_key(_FK_NAME, _TABLE_NAME, "dest_table", ["src_column"], ["dest_column"])` — giuliano-oliveira, Dec 15 '22 at 18:49

score 3 · Answer 2 · answered Mar 11 '10 at 19:23

3

I was able to accomplish this by creating a separate metadata instance and using Session.execute() to run raw SQL. Ideally, there would be a solution that uses sqlalchemy exclusively, so I wouldn't have to use MySQL-specific solutions. But as of now, I am not aware of such a solution.

answered Mar 11 '10 at 19:23

Travis

4,018
4
37
52

6

Out of curiosity, what was the SQL you executed? – a paid nerd May 16 '11 at 01:14

score 0 · Answer 3 · answered Feb 22 '11 at 16:38

I believe you can achieve this with SQLAlchemy-Migrate. Note that a ForeignKey is on an isolated column. A ForeignKeyConstraint is at the table level and relates the columns together. If you look at the ForeignKey object on the column you will see that it references a ForeignKeyConstraint.

I would unable to test this idea because of the two databases I use MS SQL isn't supported by SqlAlchemy-Migrate and sqlite doesn't support "alter table" for constraints. I did get SQLAlchemy to try to remove a FK via a drop on the references constraint on a sqlite table so it was looking good. YMMV.

score -1 · Answer 4 · answered Mar 15 '10 at 20:12

-1

Well, you can achieve this in sqlalchemy: just drop() the all the constraints before you drop() the column (theoretically, you might have multiple 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)

answered Mar 15 '10 at 20:12

van

74,297
13
168
171

I tried this out, but got an error: 'ForeignKey' object has no attribute 'drop'. I checked out the docs but didn't see any way to do this: http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html?highlight=foreignkey#sqlalchemy.schema.ForeignKey – Travis Mar 16 '10 at 16:06
this is fair, but show me the documentation of SA where Column has drop(): http://www.sqlalchemy.org/docs/reference/sqlalchemy/schema.html?highlight=foreignkey#sqlalchemy.schema.Column – van Mar 16 '10 at 16:22
'SQLAlchemy-Migrate' adds drop(), create() and other niceties but it needs to be installed separately. See http://packages.python.org/sqlalchemy-migrate/index.html – OrganicPanda Nov 18 '10 at 13:57
SQLAlchemy-Migrate adds drop() and create() to ForeignKeyConstraint not ForeignKey. – Ben Dec 05 '10 at 23:34

How do I delete a foreign key constraint in SQLAlchemy?

4 Answers4