Joined table inheritance and passive deletes

[Pedro Romano]

Hi! Tried searching around for information on this topic but couldn't
find anything, so here's the question: is it possible to use passive
deletes with joined table inheritance? Setting the
'ondelete="CASCADE"' on the foreign key declaration of the child class
primary key is trivial, however there is no explicit relationship
between child and parent class where the 'passive_deletes=True'
attribute can be set. Is there any supported way to achieve this, or
should it always be left to 'SQLAlchemy' to issue the deletes for both
tables?

Thanks in advance for any help regaeding this issue.

--Pedro.

[Michael Bayer]

Post by Pedro Romano
Hi! Tried searching around for information on this topic but couldn't
find anything, so here's the question: is it possible to use passive
deletes with joined table inheritance? Setting the
'ondelete="CASCADE"' on the foreign key declaration of the child class
primary key is trivial, however there is no explicit relationship
between child and parent class where the 'passive_deletes=True'
attribute can be set. Is there any supported way to achieve this, or
should it always be left to 'SQLAlchemy' to issue the deletes for both
tables?
Thanks in advance for any help regaeding this issue.

That's an interesting point and currently we don't have a "passive_deletes" option for joined inheritance. In the case that the child table has a primary key attribute that isn't implied by the parent , meaning, it is named differently, the ORM loads the row in based on the FK, then emits the DELETE based on the PK.

I've added ticket 2349 for this http://www.sqlalchemy.org/trac/ticket/2349 which makes the observation that at the very least, we should be able to DELETE based on the foreign key relationship directly, which would save the SELECT regardless of ON DELETE CASCADE settings. This would be a non-trivial enhancement as the whole mechanism of mapper._delete_obj() would become more complex.

A passive_deletes equivalent flag would also make mapper._delete_obj() more complex but not as much as it means we just skip the dependent table, rather than constructing a new kind of DELETE.

I've added my thoughts to that ticket.

In the meantime, the only workaround I can give you is to use query.delete() to efficiently delete rows, where ON DELETE CASCADE is needed in order to get at dependent child rows.

[Pedro Romano]

Thank you very much for the quick reply and the advice Michael.

--Pedro.

Post by Pedro Romano
Hi! Tried searching around for information on this topic but couldn't
find anything, so here's the question: is it possible to use passive
deletes with joined table inheritance? Setting the
'ondelete="CASCADE"' on the foreign key declaration of the child class
primary key is trivial, however there is no explicit relationship
between child and parent class where the 'passive_deletes=True'
attribute can be set. Is there any supported way to achieve this, or
should it always be left to 'SQLAlchemy' to issue the deletes for both
tables?
Thanks in advance for any help regaeding this issue.

That's an interesting point and currently we don't have a "passive_deletes" option for joined inheritance.     In the case that the child table has a primary key attribute that isn't implied by the parent , meaning, it is named differently, the ORM loads the row in based on the FK, then emits the DELETE based on the PK.
I've added ticket 2349 for thishttp://www.sqlalchemy.org/trac/ticket/2349 which makes the observation that at the very least, we should be able to DELETE based on the foreign key relationship directly, which would save the SELECT regardless of ON DELETE CASCADE settings.   This would be a non-trivial enhancement as the whole mechanism of mapper._delete_obj() would become more complex.
A passive_deletes equivalent flag would also make mapper._delete_obj() more complex but not as much as it means we just skip the dependent table, rather than constructing a new kind of DELETE.
I've added my thoughts to that ticket.
In the meantime, the only workaround I can give you is to use query.delete() to efficiently delete rows, where ON DELETE CASCADE is needed in order to get at dependent child rows.