Cascade

Mappers support the concept of configurable cascade behavior on relationship() constructs. This refers to how operations performed on a “parent” object relative to a particular Session should be propagated to items referred to by that relationship (e.g. “child” objects), and is affected by the relationship.cascade option.

The default behavior of cascade is limited to cascades of the so-called save-update and merge settings. The typical “alternative” setting for cascade is to add the delete and delete-orphan options; these settings are appropriate for related objects which only exist as long as they are attached to their parent, and are otherwise deleted.

Cascade behavior is configured using the cascade option on relationship():

class Order(Base):
    __tablename__ = 'order'

    items = relationship("Item", cascade="all, delete-orphan")
    customer = relationship("User", cascade="save-update")

To set cascades on a backref, the same flag can be used with the backref() function, which ultimately feeds its arguments back into relationship():

class Item(Base):
    __tablename__ = 'item'

    order = relationship("Order",
                    backref=backref("items", cascade="all, delete-orphan")
                )

The default value of cascade is save-update, merge. The typical alternative setting for this parameter is either all or more commonly all, delete-orphan. The all symbol is a synonym for save-update, merge, refresh-expire, expunge, delete, and using it in conjunction with delete-orphan indicates that the child object should follow along with its parent in all cases, and be deleted once it is no longer associated with that parent.
The list of available values which can be specified for the cascade parameter are described in the following subsections.

Delete

The delete cascade indicates that when a “parent” object is marked for deletion, its related “child” objects should also be marked for deletion. If for example we we have a relationship User.addresses with delete cascade configured:

class User(Base):
    # ...
    addresses = relationship("Address", cascade="save-update, merge, delete")

If using the above mapping, we have a User object and two related Address objects:

>>> user1 = sess.query(User).filter_by(id=1).first()
>>> address1, address2 = user1.addresses

If we mark user1 for deletion, after the flush operation proceeds, address1 and address2 will also be deleted:

>>> sess.delete(user1)
>>> sess.commit()

DELETE FROM address WHERE address.id = ?
((1,), (2,))
DELETE FROM user WHERE user.id = ?
(1,)
COMMIT

Alternatively, if our User.addresses relationship does not have delete cascade, SQLAlchemy’s default behavior is to instead de-associate address1 and address2 from user1 by setting their foreign key reference to NULL. Using a mapping as follows:

class User(Base):
    # ...
    addresses = relationship("Address")

Upon deletion of a parent User object, the rows in address are not deleted, but are instead de-associated:

>>> sess.delete(user1)
>>> sess.commit()

UPDATE address SET user_id=? WHERE address.id = ?
(None, 1)
UPDATE address SET user_id=? WHERE address.id = ?
(None, 2)
DELETE FROM user WHERE user.id = ?
(1,)
COMMIT

delete cascade is more often than not used in conjunction with delete-orphan cascade, which will emit a DELETE for the related row if the “child” object is deassociated from the parent. The combination of delete and delete-orphan cascade covers both situations where SQLAlchemy has to decide between setting a foreign key column to NULL versus deleting the row entirely.

Delete-orphan

delete-orphan cascade adds behavior to the delete cascade, such that a child object will be marked for deletion when it is de-associated from the parent, not just when the parent is marked for deletion. This is a common feature when dealing with a related object that is “owned” by its parent, with a NOT NULL foreign key, so that removal of the item from the parent collection results in its deletion.

delete-orphan cascade implies that each child object can only have one parent at a time, so is configured in the vast majority of cases on a one-to-many relationship. Setting it on a many-to-one or many-to-many relationship is more awkward; for this use case, SQLAlchemy requires that the relationship() be configured with the single_parent argument, establishes Python-side validation that ensures the object is associated with only one parent at a time.