Automatically set cascade settings based on "ON DELETE" / "ON UPDATE" when reflecting?

[Paul Molodowitch]

I noticed that sqlalchemy now properly sets the onpudate / ondelete properties of foreign keys when reflecting tables:

https://bitbucket.org/zzzeek/sqlalchemy/issue/2183/support-on-delete-update-in-foreign-key

However, it doesn't seem to set the cascade properties of relationships to reflect these properties. ie, if the Child table references the Parent table with a foreign key that has "ON DELETE CASCADE", and the reference column does not allow NULL, when you delete a parent table that has children, you will get an error, because sqlalchemy will try to set the child's ref to NULL.

ideally we should add "delete" in the relationship's cascade properties (and probably delete-orphan as well), and then set passive_updates=True.

Or am I missing something obvious  / doing something wrong / etc?

- Paul

[Mike Bayer]

the configuration of a Column or ForeignKey has never been directly
linked to how relationship() gets configured. passive_updates in
particular is a thorny one as not every database supports ON UPDATE
CASCADE, but for that matter not every database even supports ON DELETE
CASCADE. There's also lots of variants to ON UPDATE and ON DELETE and
SQLAlchemy has no awareness of any of these directly.

If we were to explore some automatic configuration of relationship based
on these attributes of ForeignKey, it would take place within the
automap extension: see
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html.

There are also recipes such that both relationship() and ForeignKey()
are generated at once, these are also good places for this kind of thing
to happen. See
https://bitbucket.org/zzzeek/pycon2014_atmcraft/src/a6d96575bc497ce0c952bb81db9c05d054c98bb5/atmcraft/model/meta/orm.py?at=master
for an example of this, I still am thinking of a way recipes like this
could also be integrated into SQLAlchemy, possibly as an enhancement to
declarative.

[Mike Bayer]

or a flag like "autoconfigure=True" on relationship(). this would also
set up innerjoin=True for joined eager loading if the FK is not null.
if the primaryjoin condition is too complex (has mulitple FKs),
autoconfigure would raise an exception.

[Paul Molodowitch]

I wasn't advocating making this connection "in general" (though I like the autoconfigure option!), but only specifically for the case of reflection - in this case, we know the DB supports it, and it would result in a better python interface to the already existing tables.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/WaVTCpBOVPk/unsubscribe.
To unsubscribe from this group and all its topics, 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/d/optout.