Delete Propagation to Parent Table for Joined Table Inheritance

[snkuranga-candid]

Describe the use case

When a polymorphic table is created via joined table inheritance, I would expect that deleting a row in the subclass table would also delete the row in the parent table. I've found that when the subclass row is explicitly deleted, the delete propgates to the parent table as I would expect. However when the subclass row is deleted via cascading delete, that deletion doesn't propagate to the parent table. This leaves us in an undesirable state with several orphaned rows in the parent table. The only clear way of handling this I've found is to use event listeners in place of a proper cascading delete but ideally there should be a way to handle this so that cascading deletes are properly carried through to the parent table.

Databases / Backends / Drivers targeted

Postgresql / psycopg

Example Use

To give an example, imagine there are three tables:

Company
-------
id

Employee
------
id

Engineer(Employee)
------
id
company_id = Column(ForeignKey("company.id", ondelete="CASCADE"))

When a row in the engineer table is explicitly deleted, the related row in the Employee table is deleted successfully

session.delete(engineer) # This deletes the related Employee row without issue

When a row in the company table is deleted, the related row in the Engineer table is deleted but that deletion doesn't propagate to the Employee table and this is what I would like to be addressed

session.delete(company) # This deletes the related Engineer row but the Employee row remains

Additional context

We're using sqlalchemy version 1.4.31

[CaselIT]

Hi,

I'm not sure how sqlalchemy should handle that case, since the foreign key cascades are handled by the db directly, meaning it's not sqlalchemy that's issuing a delete statement.