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?
answer
- the default has no delete in it
- children are de-associated, not removed
- all leaves one option out
- removing from the list as a delete
basics
~20 sThe 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 linesfrom __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 postgo deeper
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.
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.
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.
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.