Migrate Script - how to drop a ForeignKey column from a table

[a.p...@gmail.com]

Hi

I cant seem to find on the web how to remove a ForeignKey from a table, using a migrate script
I'm using Turbogears 2.2, and writing a migrrate script with "paster migrate script..."

Model:

class Yeshiva(DeclarativeBase):
    __tablename__ = 'yeshivot'

    id = Column(Integer, autoincrement=True, primary_key=True)

    start = Column(DateTime)
    end = Column(DateTime)

I want to a add a ForeignKey to the model that will be:

    organ_id = Column(Integer, ForeignKey('organs.id'))

Here is my migrate script:

from sqlalchemy import *
from migrate import *
from model import metadata

def upgrade(migrate_engine):
    metadata.bind = migrate_engine

    yeshivotTable = Table('yeshivot', metadata, autoload=True)
    organ_id = Column('organ_id',Integer, ForeignKey('organs.id'))
    organ_id.create(yeshivotTable)


def downgrade(migrate_engine):
    # Operations to reverse the above upgrade go here.
    metadata.bind = migrate_engine
    yeshivotTable = Table('yeshivot', metadata, autoload=True)
    yeshivotTable.c.organ_id.drop()

The Upgrade method works.
The Downgrade method doesn't work, and i don't know how to achive deleting that ForeignKey column

Thanks for the Help

[Michael Bayer]

well the trick with foreign keys is that they have names, and the name is needed to drop them.

So one thing is, when your schema says “ForeignKey(‘asdf’)”, and you do a CREATE TABLE, there’s no name passed; the database picks a name instead.  The format of this name is dependent on the database you’re using, you can poke around with SQL inspection tools like DbVisualizer or similar to see what name has been given to this ForeignKey - that’s the name you’d use to drop it.  A decent database like Postgresql should be naming it something that you can determine but other databases like Oracle might not produce very guessable names.

Now, another way to avoid this is to give your ForeignKey() object an explicit name upfront, like ForeignKey(‘asdf’, name=“fk_tablename_colname”), something like that.  Then, when the CREATE TABLE is emitted, that’s the name used, and then to drop it, you just use that name.

There’s a way to automate the naming convention of a ForeignKey also using the recipe detailed at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/NamingConventions.  

Thanks for the Help

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

[a.p...@gmail.com]

Thanks for the help, I need a bit more

I gave my ForeignKey a name, so far so good.
(I'm using MySQL btw)

I cant seem to find how to delete that name/column or object

In my Model:

organ_id = Column('organ_id',Integer, ForeignKey('organs.id', name='fk_yeshivot_organ_id'))

"fk_yeshivot_organ_id" is the name of my ForeignKey object,


I tryed, but this doesn't work:

def downgrade(migrate_engine):

    metadata.bind = migrate_engine
    yeshivotTable = Table('yeshivot', metadata, autoload=True)

    yeshivotTable.c.fk_yeshivot_organ_id.drop()

I get AttribureError, beacuse yeshivotTable.c doesn't have fk_yeshivot_organ_id, so where shoud it be, and how to drop it?

[a.p...@gmail.com]

Ok, got it!
Thanks for the help

def upgrade(migrate_engine):
    metadata.bind = migrate_engine
    yeshivotTable = Table('yeshivot', metadata, autoload=True)

    organ_id = Column('organ_id',Integer, ForeignKey('organs.id', name='fk_yeshivot_organ_id'))
    organ_id.create(yeshivotTable)


def downgrade(migrate_engine):

    metadata.bind = migrate_engine
    yeshivotTable = Table('yeshivot', metadata, autoload=True)

    cons = ForeignKeyConstraint([yeshivotTable.c.organ_id],[Organ.__table__.c.id],name='fk_yeshivot_organ_id')
    cons.drop()
    yeshivotTable.c.organ_id.drop()

The trick was createing a ForeignKeyConstraint, and using the name i gave my ForeignKey. i tryed that before, but now that i had a specfic name, i was able to drop it. after dropping the ForeignKeyConstraint  i also needed to drop the column (organ_id) from the table too.

Again, thanks for the help,
and reading the docs here - https://sqlalchemy-migrate.readthedocs.org/en/v0.7.1/changeset.html

[Michael Bayer]

that would be op.drop_constraint(“fk_yeshivot_organ_id”, “yeshivot”, type_=“foreign

key”)

http://alembic.readthedocs.org/en/latest/ops.html#alembic.operations.Operations.drop_constraint

[Michael Bayer]

On Jan 22, 2014, at 11:05 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

On Jan 22, 2014, at 6:55 AM, a.p...@gmail.com wrote:

Thanks for the help, I need a bit more

I gave my ForeignKey a name, so far so good. 
(I'm using MySQL btw)

I cant seem to find how to delete that name/column or object

In my Model:

organ_id = Column('organ_id',Integer, ForeignKey('organs.id', name='fk_yeshivot_organ_id'))

"fk_yeshivot_organ_id" is the name of my ForeignKey object,


I tryed, but this doesn't work:

def downgrade(migrate_engine):
    metadata.bind = migrate_engine
    yeshivotTable = Table('yeshivot', metadata, autoload=True)
    yeshivotTable.c.fk_yeshivot_organ_id.drop()

I get AttribureError, beacuse yeshivotTable.c doesn't have fk_yeshivot_organ_id, so where shoud it be, and how to drop it?

that would be op.drop_constraint(“fk_yeshivot_organ_id”, “yeshivot”, type_=“foreign

key”)

http://alembic.readthedocs.org/en/latest/ops.html#alembic.operations.Operations.drop_constraint

oh, you’re on Migrate.   Sorry, I haven’t used migrate in many years I assumed this was alembic.