Configuring cascade delete and delete-orphan correctly

Set cascade="all, delete-orphan" on the parent side of a composition relationship, add ondelete="CASCADE" to the child's foreign key and passive_deletes=True to the relationship — the first makes the ORM delete children, the last two let the database do it in one statement instead of loading every row. This guide belongs to modeling relationships, cascades and association objects.

Quick Answer

SQLAlchemy's default cascade does not delete children. It sets their foreign key to NULL, which fails immediately against a NOT NULL column.

Before — the default cascade against a NOT NULL foreign key:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    lines: Mapped[list["OrderLine"]] = relationship(back_populates="order")


class OrderLine(Base):
    __tablename__ = "order_lines"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"))
    order: Mapped[Order] = relationship(back_populates="lines")


await session.delete(order)
await session.commit()
# sqlalchemy.exc.IntegrityError: (asyncpg.exceptions.NotNullViolationError)
# null value in column "order_id" of relation "order_lines" violates not-null constraint

After — composition declared on both sides:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    lines: Mapped[list["OrderLine"]] = relationship(
        back_populates="order",
        cascade="all, delete-orphan",   # the ORM owns the children
        passive_deletes=True,           # but lets the database delete them
    )


class OrderLine(Base):
    __tablename__ = "order_lines"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id", ondelete="CASCADE"))
    order: Mapped[Order] = relationship(back_populates="lines")


await session.delete(order)
await session.commit()
# DELETE FROM orders WHERE id = $1   — the database removes the lines

All three settings belong together. cascade tells the unit of work the children are owned; ondelete="CASCADE" puts the rule in the schema; passive_deletes=True tells SQLAlchemy it may rely on that rule instead of loading each child.

Execution Context & Async Workflow Integration

A cascade is a statement about ownership. save-update and merge, the default, only say that children follow their parent into a session. delete says a parent's deletion extends to its children. delete-orphan says a child that is removed from its parent's collection, or reassigned to another parent, has no reason to exist and should be deleted.

Without delete, the unit of work still has to do something with the children when their parent goes away, and what it does is disassociate them: it loads the collection and sets each child's foreign key to NULL. On a nullable column that leaves orphan rows behind; on a NOT NULL column it fails, which is the error above. Where the child's foreign key is part of its primary key, SQLAlchemy refuses outright with AssertionError: Dependency rule tried to blank-out primary key column 'order_lines.order_id'.

Under async, the loading is the part that bites. An ORM cascade delete loads every collection it needs to cascade into, and if those collections are not already loaded, that is a lazy load — which under AsyncSession raises MissingGreenlet: greenlet_spawn has not been called. Three ways out, in order of preference: passive_deletes=True with ON DELETE CASCADE so nothing needs loading; eager loading with selectinload(Order.lines) before the delete; or await session.refresh(order, ["lines"]). The general rule behind all of them is in fixing GreenletSpawnError in async SQLAlchemy workflows.

passive_deletes=True changes what SQLAlchemy emits, not what it means. It stops the ORM loading and deleting children, and trusts the database's ON DELETE rule to remove them. If the schema has no such rule, the child rows survive — or the delete fails on the foreign key — so the two must be declared together. Alembic will not add ondelete to an existing constraint by itself; that is a drop and re-add of the foreign key, which managing enums, constraints and indexes in migrations covers.

One thing no cascade reaches is a set-based delete. await session.execute(delete(Order).where(...)) is one statement; the ORM never loads the orders, so delete-orphan does not run and only the database's own ON DELETE rules apply. That is another reason to put the rule in the schema as well as in the model.

Resolving Warnings, Errors & Common Mistakes

The expected to delete 3 row(s); Only 0 were matched warning is worth understanding rather than silencing, because it means two mechanisms are fighting. The database removed the children as part of the parent's delete, and the ORM then issued its own DELETE for rows that were already gone. Nothing is corrupted, but the extra statements are wasted and the warning hides real mismatches — such as a row deleted by another transaction — that the same message would otherwise report.

The many-to-many restriction reflects a real ambiguity. In a secondary relationship, the rows on the other side are typically shared: deleting a Tag because it was removed from one Post would be wrong. single_parent=True asserts that sharing does not happen, and SQLAlchemy then allows delete-orphan. When the association itself carries data, the right answer is an association object, covered in using association objects for many-to-many with extra columns.

Advanced: Choosing Between ORM and Database Cascades

Both mechanisms delete children; they differ in who does the work and what else runs.

An ORM cascade loads each child and deletes it individually, so before_delete and after_delete mapper events fire per row, history tables and search indexes maintained in Python stay correct, and delete-orphan works for collection removals — something no database rule can express. The cost is one SELECT per collection plus one DELETE per level, and memory for every child.

A database cascade is a single statement. It covers deletes issued by anything — other services, a psql session, a bulk delete() — and its cost does not grow with collection size. What it cannot do is run Python.

from sqlalchemy import ForeignKey, event
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    # Composition, deleted by the database, with the ORM aware of the rule.
    lines: Mapped[list["OrderLine"]] = relationship(
        back_populates="order", cascade="all, delete-orphan", passive_deletes=True
    )
    # Not composition: an order's customer outlives it.
    customer_id: Mapped[int] = mapped_column(ForeignKey("customers.id", ondelete="RESTRICT"))

ondelete="RESTRICT" on the other relationship is the counterpart worth adopting deliberately: it makes the database refuse to delete a customer who still has orders, turning a data-integrity mistake into an error instead of a cascade nobody intended.

When per-row Python must run and collections are large, do the work in bulk before the delete rather than per row:

from sqlalchemy import delete, select

from shop.models import Order, OrderLine
from shop.search import remove_documents


async def delete_order(session, order_id: int) -> None:
    line_ids = (await session.scalars(
        select(OrderLine.id).where(OrderLine.order_id == order_id)
    )).all()
    await remove_documents("order_line", line_ids)     # one call, not one per row
    await session.execute(delete(Order).where(Order.id == order_id))
    await session.commit()

Soft deletion is the third option, and often the right one for anything a user might want back. Instead of cascading, mark rows deleted and filter them out of every query — the pattern in filtering soft-deleted rows with with_loader_criteria, which applies the filter to eager loads as well as to top-level queries.

Testing Cascades Before They Reach Production

Cascade bugs are asymmetric: the error cases fail loudly in development, while the quiet ones — orphan rows left behind, passive_deletes without a schema rule — surface months later as data nobody can explain. Two small tests per composition relationship cover both.

The first deletes a parent and asserts the children are gone from the database, not from the session. Querying in a fresh session after the commit is what makes it meaningful:

import pytest
from sqlalchemy import func, select

from shop.models import Order, OrderLine


@pytest.mark.asyncio
async def test_deleting_an_order_deletes_its_lines(session_factory, order_factory):
    order_id = await order_factory(line_count=3)

    async with session_factory() as session:
        order = await session.get(Order, order_id)
        await session.delete(order)
        await session.commit()

    async with session_factory() as session:
        remaining = await session.scalar(
            select(func.count(OrderLine.id)).where(OrderLine.order_id == order_id)
        )
    assert remaining == 0


@pytest.mark.asyncio
async def test_removing_a_line_deletes_the_row(session_factory, order_factory):
    order_id = await order_factory(line_count=3)

    async with session_factory() as session:
        order = await session.get(Order, order_id, options=[selectinload(Order.lines)])
        order.lines.remove(order.lines[0])       # delete-orphan
        await session.commit()

    async with session_factory() as session:
        remaining = await session.scalar(
            select(func.count(OrderLine.id)).where(OrderLine.order_id == order_id)
        )
    assert remaining == 2

The second test is the one that catches a missing delete-orphan: without it, removing the line only nulls its foreign key, and the count stays at three — or the commit fails.

A schema-level check is worth adding once, for the whole model. passive_deletes=True is a promise about the database, and a test can verify the promise holds for every relationship that makes it:

import pytest
from sqlalchemy import inspect

from shop.models import Base


@pytest.mark.asyncio
async def test_passive_deletes_relationships_have_ondelete_cascade(migrated_engine):
    async with migrated_engine.connect() as conn:
        rules = await conn.run_sync(lambda c: {
            (t, fk["constrained_columns"][0]): (fk.get("options") or {}).get("ondelete")
            for t in inspect(c).get_table_names()
            for fk in inspect(c).get_foreign_keys(t)
        })

    for mapper in Base.registry.mappers:
        for rel in mapper.relationships:
            if not rel.passive_deletes:
                continue
            for column in rel.remote_side:
                if column.primary_key:
                    continue
                actual = rules.get((column.table.name, column.name))
                assert actual and actual.upper() == "CASCADE", (
                    f"{mapper.class_.__name__}.{rel.key} uses passive_deletes, but "
                    f"{column.table.name}.{column.name} has ON DELETE {actual!r}"
                )

Run it against a database built by the migrations, as in running tests against a Postgres testcontainer, so it checks the schema you actually deploy rather than the one create_all() would build.

Frequently Asked Questions

Related

• Modeling Relationships, Cascades and Association Objects — The parent guide: relationship shapes, loading and ownership.
• Using association objects for many-to-many with extra columns — Where delete-orphan belongs in a many-to-many model.
• Modeling self-referential relationships for trees — Cascades down a hierarchy of unknown depth.
• Using selectinload vs joinedload for N+1 prevention — Loading the collections a cascade would otherwise load one at a time.