adding ondelete="CASCADE"

[kristian kvilekval]

I was trying to modify an existing table to add the ondelete="CASCADE"

While trying the  script below I received the error:

sqlalchemy.exc.InvalidRequestError: Table 'taggable' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

Not exactly sure where to add this "extend_existing=True"?

Thanks..

def upgrade():

    op.create_foreign_key("taggable_children_fk", 

                          'taggable', 'taggable', 

                          ["resource_parent_id"], ["id"],

                          ondelete="CASCADE")

[Michael Bayer]

that is not related to the "ondelete", it's because the foreign key is self-referential and it's a bug in Alembic. It is fixed as of the latest tip which you can get at https://bitbucket.org/zzzeek/alembic/get/484c7b9fc3ec.tar.gz , and will be in release 0.3.6.

[kristian kvilekval]

Great.. fetching the new versions worked.. Thanks.

[kristian kvilekval]

A related question to my first.. how do I find existing foreign keys to modify/delete?

I would like to add ondelete cascade to several foreign keys.  I am trying to write a script 

that will do this.  However, the foreign key names appear to be different for different databases (i've checked

mysql and postgres).. Is there a way to find the foreign keys on a table in alembic so 

I can drop or alter the old FKs ? 

Thx

[Michael Bayer]

ideally you'd have created those foreign keys giving them an explicit name. otherwise, there's two other avenues. One is to use the Inspector, like this:

from sqlalchemy.engine.reflection import Inspector
insp = Inspector.from_engine(op.get_bind())
fks = insp.get_foreign_keys("tablename")

that would give you back a list of dictionaries, the fields in each are listed here: http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html?highlight=inspector#sqlalchemy.engine.reflection.Inspector.get_foreign_keys

using the inspector requires a live connection though, so won't work in --sql mode.

If you needed to work in --sql mode, you'd need to devise a SQL sequence that declares variables and queries for the database's schema information which are then passed to the ALTER command. This would need to be all specific to the database. These are fairly tedious to come up with and you'd need to google/read docs to work them out, for example here's one Alembic does for SQL server, I've stuck it inside of op.execute() to illustrate:

op.execute("""
declare @const_name varchar(256)
select @const_name = [name] from sys.check_constraints
where parent_object_id = object_id('sometable')
and col_name(parent_object_id, parent_column_id) = 'somecolumn'

exec('alter table sometable drop constraint ' + @const_name)
""")