Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 37 ยท Relationships and Loading

Cascades, Orphans and Self-Referential Relationships

Decide what happens to child rows when a parent is saved or deleted: cascade options, delete-orphan, passive_deletes, ON DELETE, plus adjacency-list trees.

When you delete an author, what should happen to their books? When you add an author to a session, should their new books be saved too? These questions are answered by cascades: rules on a relationship() that say which session operations flow from a parent to its children.

You will also meet the other special shape of this chapter: the self-referential relationship, where a table points at itself (employees and managers, comments and replies, category trees).

What cascade means

A cascade is a list of operations that propagate along the relationship. The default is "save-update, merge", which is why session.add(author) also saves new books you appended. The full list:

Cascade Meaning
save-update Adding the parent to a session also adds children attached to it
merge session.merge(parent) also merges children
expunge Detaching the parent detaches its children
delete Deleting the parent deletes the children (ORM issues the DELETEs)
delete-orphan A child removed from the parent's collection is deleted
refresh-expire Refreshing or expiring the parent does the same to children
all Shorthand for everything above except delete-orphan

The two you will use most are "all, delete-orphan" for owned children (order lines belong to an order) and the default for referenced objects (a book's publisher outlives the book).

Default behavior: deleting a parent nulls the children

Python Default: children are detached, not deleted

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The books survive with author_id = NULL. The ORM loaded the collection, then issued UPDATE book SET author_id=NULL for each child. This is a safe default, but wrong for children that make no sense without their parent.

cascade="all, delete-orphan"

Declare ownership on the parent side. Now deleting the parent deletes the children, and removing a child from the list deletes just that child.

Python Owned children with delete-orphan

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

delete-orphan has one rule that surprises people: a child can no longer exist without a parent. Creating Line(sku="X") and flushing without attaching it to an order raises an error. Use it only where that is true.

delete-orphan is only valid for one-to-many and one-to-one relationships. If a child can belong to several parents (a many-to-many tag), do not cascade deletes to it.

Let the database do the cascading

With cascade="all" the ORM loads every child into memory just to delete it. For large collections that is wasteful. Put ON DELETE CASCADE on the foreign key and tell the ORM to trust it with passive_deletes=True:

Python
class Order(Base):
    __tablename__ = "order_"
    id: Mapped[int] = mapped_column(primary_key=True)
    lines: Mapped[list["Line"]] = relationship(
        cascade="all, delete-orphan", passive_deletes=True
    )

class Line(Base):
    __tablename__ = "line"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("order_.id", ondelete="CASCADE"))

Now session.delete(order) sends a single DELETE FROM order_ and the database removes the lines. SQLite ignores foreign keys unless you run PRAGMA foreign_keys=ON on each connection, so on SQLite add a connect event listener that runs it (the Session Patterns and Events lesson shows the event API). PostgreSQL and MySQL enforce them by default.

Self-referential relationships

A table can reference itself. A Category has a parent_id pointing at another Category. This is the adjacency list pattern. The only trick is telling SQLAlchemy which side is the "one" side, using remote_side.

flowchart TD
    A["Electronics (parent_id NULL)"] --> B["Phones"]
    A --> C["Laptops"]
    B --> D["Android"]
    B --> E["iOS"]

One table, every row may point at its parent row in the same table.

Python A category tree

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

remote_side=[id] marks the id column as the "remote" (parent) end. Without it SQLAlchemy would assume parent is the one-to-many side and the tree would be upside down.

Walking a whole tree in one query

Lazy loading a deep tree runs one query per node. Two fixes exist. selectinload(Category.children, recursion_depth=-1) loads children level by level, one query per level. For a single query, use a recursive CTE:

Python Recursive CTE for the whole subtree

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What happens by default when you delete a parent whose children are mapped with a plain relationship()?

    Without a delete cascade the ORM loads the children and sets their foreign key to NULL before deleting the parent.
  2. 2. What does cascade='all, delete-orphan' add compared with the default?

    'all' includes delete, and delete-orphan removes a child as soon as it leaves the collection.
  3. 3. What is passive_deletes=True for?

    With passive_deletes the ORM trusts the foreign key's ON DELETE rule instead of loading and deleting each child.
  4. 4. Which argument tells SQLAlchemy which side of a self-referential relationship is the parent ('one') side?

    remote_side marks the remote column of the self join, which for a parent_id tree is the id column.

Summary

  • Cascades decide which session operations travel from a parent to its children.
  • The default (save-update, merge) saves children but, on delete, only nulls their foreign key.
  • Use cascade="all, delete-orphan" for children that belong to exactly one parent.
  • passive_deletes=True plus ondelete="CASCADE" lets the database delete children without loading them.
  • Self-referential trees need remote_side on the many-to-one side; use selectinload or a recursive CTE to read whole subtrees efficiently.

Next: Sessions and the Unit of Work