FastAPI Postgres Cascade Delete

Question

I've 2 table, "Book" and "Bookmark".
How to set a cascade on models so when they delete "Book", they will also delete "Bookmark" based on "Book".

Here's the models:

class Book(Base):
    __tablename__ ="book"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column(String, nullable=False)
    description = Column(String, nullable=True)

    # relation
    r_bookmark = relationship("BookMark", back_populates="r_book")


class BookMark(Base):
    __tablename__ ="bookmark"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column(String, nullable=False)
    description = Column(String, nullable=True)
    book_id = Column(Integer, ForeignKey("book.id", ondelete='CASCADE'), nullable=False)

    # relation
    r_book = relationship("Book", back_populates="r_bookmark", cascade="all,delete")

Please help, thanks.

https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete - usually you'll set `cascade` on the `relationship` in the parent, and SQLAlchemy will handle it for you. Also, it'd be helpful if you said what doesn't work or what happens with your current code, since it seems like you're already trying to use CASCADE with the `ForeignKey`. — MatsLindh, Jul 12 '22 at 10:41
@MatsLindh i was search and following the same article as your comment, but what i was done is add `cascade` to `relation` for the child table, but not the parent table. When i add it to parent table instead, it's work perfectly. Many thanks. — fudu, Jul 13 '22 at 02:17

fudu · Accepted Answer · 2022-07-13T02:53:54.843

Thanks to MatsLindh, here's the correct answer:

class Book(Base):
    __tablename__ ="book"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column(String, nullable=False)
    description = Column(String, nullable=True)

    # relation
    r_bookmark = relationship("BookMark", back_populates="r_book", cascade="all,delete")


class BookMark(Base):
    __tablename__ ="bookmark"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column(String, nullable=False)
    description = Column(String, nullable=True)
    book_id = Column(Integer, ForeignKey("book.id"), nullable=False)

    # relation
    r_book = relationship("Book", back_populates="r_bookmark")

I've add the cascade to the relation on the parent table, NOT the child table.

FastAPI Postgres Cascade Delete

1 Answers1