Do passive deletes apply to many to many relationships?

[Randy Syring]

I am trying to get SQLAlchemy to let my database's foreign keys "on delete cascade" do the cleanup on the association table between two objects. I have setup the cascade and passive_delete options on the relationship as seems appropriate from the docs. However, when a related object is loaded into the collection of a primary object and the primary object is deleted from the session, then SQLAlchemy issues a delete statement for the related object.

I have a code example to reproduce the problem at stackoverflow: http://stackoverflow.com/questions/23669198/passive-deletes-in-sqlalchemy-with-a-many-to-many-relationship-dont-prevent-del

Is there a way to configure SQLAlchemy to never emit a DELETE for a related object even when that object is loaded in a collection of an entity that is deleted from the session?

[Michael Bayer]

On May 15, 2014, at 12:28 AM, Randy Syring <ra...@thesyrings.us> wrote:

I am trying to get SQLAlchemy to let my database's foreign keys "on delete cascade" do the cleanup on the association table between two objects. I have setup the cascade and passive_delete options on the relationship as seems appropriate from the docs. However, when a related object is loaded into the collection of a primary object and the primary object is deleted from the session, then SQLAlchemy issues a delete statement for the related object.

there’s a setting known as “passive_deletes=‘all’” which is intended not just to block the relationship from emitting a SELECT for rows to be deleted, but also prevents the DELETE from taking place entirely.   However, the “all” part of this feature is not implemented for many-to-many relationships right now - while a many-to-many with passive_deletes=True will forego emitting a SELECT for unloaded rows, it still emits DELETE for those rows which it knows about in memory.  It does at least emit the DELETE within an “executemany” so is an efficient operation.

The delete for the related object here only occurs when cascade=“delete” is set.  If you didn’t have this cascade set up, the delete of the primary object would only delete the association rows, which is appropriate.  if these association rows are actually mapped elsewhere in some more elaborate way then you shouldn’t be using “secondary” in this way, you’d use the association object pattern.

I have a code example to reproduce the problem at stackoverflow: http://stackoverflow.com/questions/23669198/passive-deletes-in-sqlalchemy-with-a-many-to-many-relationship-dont-prevent-del

Is there a way to configure SQLAlchemy to never emit a DELETE for a related object even when that object is loaded in a collection of an entity that is deleted from the session?

OK so here, the “related object” is *not* being deleted.  That would be “tags”.  the “blog_tags_table” is not an object, it is a many-to-many table.

from that code given you would just apply viewonly=True to the relationship.