Deleting many tables when one specific table is deleted in FLASK SQLAlchemy-CodePudding

I have an SQLAlchemy VolunteerClient Model which is a join table:

class VolunteerClient(Base):
    __tablename__ = 'volunteer_client'

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    volunteer_id = Column(Integer, ForeignKey('provider_user.user_id', onupdate='CASCADE', ondelete='RESTRICT'), unique=True)
    client_id = Column(Integer, ForeignKey('user.id', onupdate='CASCADE', ondelete='RESTRICT'), unique=True)

and a VolunteerReport model:

class VolunteerReport(Base):
    __tablename__ = 'volunteer_report'

    id = Column(Integer, primary_key=True, autoincrement=True, index=True)
    volunteer_id = Column(Integer, ForeignKey('volunteer_client.volunteer_id', cascade="all, delete"))
    client_id = Column(Integer, ForeignKey('volunteer_client.client_id', cascade="all, delete"))
    report = Column(String(255), nullable=False)
    report_category = Column(String(255), nullable=False)

If I was to delete a VolunteerClient table, which essentially unassignes a volunteer from a client but does not actually delete the users which they represent. with a specific volunteer_id and client_id set,

Example: I delete VolunteerClient table where: volunteer_id = 1, & client_id = 1,

I want any and all VolunteerReports where: volunteer_id = 1, & client_id = 1 to be deleted as well. Have I set this up correctly with the FK references to volunteer_client and the cascade='all, delete'?

Any advice would be awesome.

CodePudding user response：

To automatically delete child rows when a parent is deleted, you need to set ondelete='CASCADE' on the foreign key defined in the child table. In this case, the foreign key is a composite key as it consists of volunteer_id and client_id. This means you need a unique constraint over the columns in the parent as well. This simplified version of your models show how it would work (I've removed the FK definitions from VolunteerClient the tables are defined in the question).

class VolunteerClient(Base):
    __tablename__ = 'volunteer_client'

    id = sa.Column(sa.Integer, primary_key=True)
    volunteer_id = sa.Column(sa.Integer)
    client_id = sa.Column(sa.Integer)

    __table_args__ = (sa.UniqueConstraint(volunteer_id, client_id),)


class VolunteerReport(Base):
    __tablename__ = 'volunteer_report'

    id = sa.Column(sa.Integer, primary_key=True)
    volunteer_id = sa.Column(sa.Integer)
    client_id = sa.Column(sa.Integer)

    __table_args__ = (
        sa.ForeignKeyConstraint(
            [volunteer_id, client_id],
            [VolunteerClient.volunteer_id, VolunteerClient.client_id],
            ondelete='CASCADE',
        ),
    )

You can also configure delete cascades on SQLAlchemy relationships, which give you more control over what happens when a parent row is deleted. As you don't seem to be using relationships, and the database cascade does what you want I won't cover that in this answer.