skip to content

With SQLAlchemy's default relationship() cascade, what happens to a Post's Comments when the post is deleted, and what does "all, delete-orphan" change?

level: middleimportance: must knowfreq 48%

answer

  1. the default has no delete in it
  2. children are de-associated, not removed
  3. all leaves one option out
  4. removing from the list as a delete

basics

~20 s

The default cascade is "save-update, merge", so deleting a Post sets each Comment's post_id to NULL, failing on a NOT NULL key. "all, delete-orphan" deletes the comments with the post and deletes any comment removed from post.comments.

solid answer

~40 s

`relationship()` defaults to `cascade="save-update, merge"`, which has no delete. On `session.delete(post)` SQLAlchemy loads `post.comments` and emits `UPDATE comments SET post_id=NULL` for each one, so a `NOT NULL` foreign key turns that into an `IntegrityError`. `"all"` means save-update, merge, refresh-expire, expunge and delete: the comments are deleted with the post, but a comment removed from the collection is still only nulled. Adding `delete-orphan` makes removal delete the row too, so `post.comments.remove(c)` becomes the way to delete a comment. It belongs on the one-to-many side; on a many-to-one it raises an `ArgumentError` unless `single_parent=True`. None of this applies to a `delete()` statement, which bypasses the unit of work.

code

python · 43 lines
python
from __future__ import annotations

from sqlalchemy import ForeignKey, String, create_engine, event, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    comments: Mapped[list[Comment]] = relationship(
        back_populates="post", cascade="all, delete-orphan"
    )


class Comment(Base):
    __tablename__ = "comments"
    id: Mapped[int] = mapped_column(primary_key=True)
    body: Mapped[str] = mapped_column(String(500))
    post_id: Mapped[int] = mapped_column(ForeignKey("posts.id"))
    post: Mapped[Post] = relationship(back_populates="comments")


engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    session.add(Post(comments=[Comment(body="a"), Comment(body="b"), Comment(body="c")]))


@event.listens_for(engine, "before_cursor_execute")
def show(conn, cursor, statement, parameters, context, executemany):
    print(statement.replace("\n", ""), parameters)


with Session(engine) as session, session.begin():
    post = session.get(Post, 1)
    post.comments.remove(post.comments[0])  # orphan: DELETE at flush
    session.flush()
    session.delete(post)  # delete cascade: remaining comments, then the post

go deeper

for a junior

Recall that the default cascade is save-update, merge, with no delete, and that all, delete-orphan is the usual setting for children a parent owns.

for a middle

Explain the three outcomes, NULL the key, delete with the parent, delete when removed, and which cascade value produces each, including why all alone still nulls removed children.

for a senior

Diagnose the IntegrityError on post_id from a missing delete cascade, and know the limits: only Session.delete() cascades, and unloaded children are SELECTed before they are deleted.

for a principal

Decide where deletion rules live, ORM cascade or database ON DELETE, and make the cascade string a reviewed part of every owned-children mapping.

## The default cascade is "save-update, merge" Every `relationship()` carries a `cascade` setting that decides which `Session` operations travel from a parent to its related objects. When you pass nothing, it is `"save-update, merge"`: - **save-update**: `session.add(post)` also adds the comments in `post.comments`, and a comment appended to a post already in a `Session` joins that `Session`. - **merge**: `Session.merge(post)` merges the related comments too. - There is **no delete** in the default. Deleting a post does not delete its comments. So what does SQLAlchemy do with the comments when the post goes? It **de-associates** them. At flush it loads the collection if it is not loaded, emits `UPDATE comments SET post_id=NULL` for each comment, then deletes the post. If `comments.post_id` is `NOT NULL` (it is, for `post_id: Mapped[int]`), the database rejects that UPDATE with an `IntegrityError`, which is often the first sign that cascade was never configured. ## The settings side by side `"all"` is shorthand for `"save-update, merge, refresh-expire, expunge, delete"`. It does **not** include `delete-orphan`, which is why the usual setting for owned children is `"all, delete-orphan"`. | `cascade` on `Post.comments` | `session.delete(post)` | `post.comments.remove(c)` | |---|---|---| | default `"save-update, merge"` | `UPDATE comments SET post_id=NULL` for each comment | `UPDATE ... SET post_id=NULL` for `c` | | `"all"` | `DELETE` for each comment, then the post | `UPDATE ... SET post_id=NULL` for `c` | | `"all, delete-orphan"` | `DELETE` for each comment, then the post | `DELETE` for `c` | With a `NOT NULL` foreign key, every `SET post_id=NULL` cell above ends in an `IntegrityError`. That is how `"all"` alone still breaks when someone removes a comment from the list. ## How delete-orphan decides a comment is an orphan `delete-orphan` adds one rule to `delete`: a child that is **de-associated** from its parent is marked for deletion as if it had been passed to `Session.delete()`. The flow is: 1. Code removes `c` from `post.comments` (or replaces the collection, or deletes an item by index). 2. The attribute history records that `c` lost its parent. 3. At flush, the unit of work sees an orphan and emits `DELETE FROM comments WHERE comments.id = ?`. The model assumes each child has **one** parent at a time, so `delete-orphan` belongs on the one-to-many side. Put it on the many-to-one `Comment.post` and mapper configuration raises an `ArgumentError` whose message begins "For many-to-one relationship Comment.post, delete-orphan cascade is normally configured only on the "one" side of a one-to-many relationship". The escape hatch, `single_parent=True`, adds a Python-side check that only one object refers to the target at a time; it fits a schema where in practice only one row points at each target, such as a one-to-one, not a real many-to-one. ## Removing from the collection is the delete API The ORM in general does not edit a collection during a flush. If you call `session.delete(c)` directly, the row is deleted but `c` stays in `post.comments` until the post is expired, which normally happens at commit. With `delete-orphan` configured, the idiomatic delete is the other way round: remove the comment from `post.comments` and let the cascade delete the row. Memory and database then agree. ## Where cascades stop - They apply only to objects going through `Session.delete()` and the unit of work. A `delete(Post).where(...)` statement run with `Session.execute()` deletes posts in SQL and never touches `Post.comments`; only a database `ON DELETE` rule helps there. - To delete or null the children, the ORM needs them in memory, so an unloaded collection is loaded with a SELECT first. Handing that work to an `ON DELETE CASCADE` foreign key is what `passive_deletes=True` is for. - `"all"` includes `refresh-expire`, so expiring or refreshing a post also expires its loaded comments. Spell out `"save-update, merge, delete, delete-orphan"` if you do not want that. ## Choosing the setting for a relationship The cascade string is a statement about who owns the children, so pick it per relationship: - **Children that cannot exist without the parent**, like comments on a post: `cascade="all, delete-orphan"` on the parent's collection, with a `NOT NULL` foreign key. - **Children that should outlive the parent**: keep the default, make the foreign key nullable, and let SQLAlchemy null it when the parent goes. - **Children that must block the parent's deletion**: keep the default with a `NOT NULL` foreign key; the nulling UPDATE fails and the delete is refused. - **Very large child sets**: keep the delete cascade but add `passive_deletes=True` with an `ON DELETE CASCADE` foreign key, so the database removes the rows instead of the ORM loading them.

  • Why can't delete-orphan go on Comment.post instead of Post.comments?
    delete-orphan tracks orphans created when one parent loses a child. On the many-to-one side it would mean deleting the Post once no Comment refers to it, which the model does not support, so SQLAlchemy raises an `ArgumentError` at mapper configuration. `single_parent=True` silences it by asserting only one Comment may refer to a Post at a time, which fits a one-to-one, not this mapping.
  • After session.delete(comment) and a flush, why is the comment still in post.comments?
    The ORM in general does not edit collections during a flush; the row is gone but the Python list is unchanged until the post is expired, normally at commit. With delete-orphan configured, remove the comment from `post.comments` instead, and the cascade deletes the row while memory and database agree.

The default cascade treats comments like library books whose borrower has left: the books stay on the shelf with no borrower recorded. delete-orphan treats them like items in a shopping cart: take one out of the cart, or throw the cart away, and the item is gone.

  • The default relationship() cascade deletes children along with their parent.
  • cascade="all" already includes delete-orphan.
  • delete-orphan belongs on the many-to-one side, Comment.post.
  • A delete(Post) statement through Session.execute() honours delete cascade.
  • Removing a comment from post.comments never changes the database.