How to enable delete on cascade for a many-to-one relationship?

[jymchng]

Hi @jonra1993

I have two tables, Project and Category. One row in Project can be linked to many rows in Category, i.e. one project can belong to many categories.

Project.categories: List[Category] and Category.project: Project.

Right now, this is my Category table:

class Category(BaseUUIDModel, CategoryBase, table=True):
    # pass
    project: Optional["Project"] = Relationship(back_populates="categories", sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "Category.project_id==Project.id",
            'cascade': 'all, delete'})

And this is my Project table:

class Project(BaseIntModel, ProjectBase, table=True):  # PROJECT ID IS INT!!!
    categories: List[Category] = Relationship(
        back_populates='project',
        sa_relationship_kwargs={
            "lazy": "selectin",
            'cascade': 'all,delete',
            "primaryjoin": "Category.project_id==Project.id",
        })

What sa_relationship_kwargs I need to change in order to have this effect that whenever I delete a record in Project, all rows in Category such that Category.project_id == Project.id are deleted?

[jonra1993]

Hello @jymchng did you tried cascade: "all, delete-orphan" as here

Or try this

class Project(BaseIntModel, ProjectBase, table=True):
    categories: List[Category] = Relationship(
        back_populates='project',
        sa_relationship_kwargs={
            "lazy": "selectin",
            'cascade': 'all,delete',
            "passive_deletes": True,  # Add this line to enable passive deletes
            "primaryjoin": "Category.project_id==Project.id",
        })

class Category(BaseUUIDModel, CategoryBase, table=True):
    project: Optional["Project"] = Relationship(
        back_populates="categories",
        sa_relationship_kwargs={
            "lazy": "selectin",
            "primaryjoin": "Category.project_id==Project.id",
            "passive_deletes": True,  # Add this line to enable passive deletes
        })