Cascades

Mappers support the concept of configurable cascade behavior onrelationship() constructs. This refersto how operations performed on a “parent” object relative to aparticular Session should be propagated to itemsreferred to by that relationship (e.g. “child” objects), and isaffected by the relationship.cascade option.

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

Cascade behavior is configured using thecascade option onrelationship():

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 thebackref() function, which ultimately feedsits arguments back into relationship():

class Item(Base):
    __tablename__ = 'item'
 
    order = relationship("Order",
                    backref=backref("items", cascade="all, delete-orphan")
                )

The Origins of Cascade

SQLAlchemy’s notion of cascading behavior on relationships,as well as the options to configure them, are primarily derivedfrom the similar feature in the Hibernate ORM; Hibernate refersto “cascade” in a few places such as inExample: Parent/Child.If cascades are confusing, we’ll refer to their conclusion,stating “The sections we have just covered can be a bit confusing.However, in practice, it all works out nicely.”

The default value of cascade is save-update, merge.The typical alternative setting for this parameter is eitherall or more commonly all, delete-orphan. The all symbolis a synonym for save-update, merge, refresh-expire, expunge, delete,and using it in conjunction with delete-orphan indicates that the childobject should follow along with its parent in all cases, and be deleted onceit is no longer associated with that parent.

The list of available values which can be specified forthe cascade parameter are described in the following subsections.

save-update

save-update cascade indicates that when an object is placed into aSession via Session.add(), all the objects associatedwith it via this relationship() should also be added to thatsame Session. Suppose we have an object user1 with tworelated objects address1, address2:

>>> user1 = User()
>>> address1, address2 = Address(), Address()
>>> user1.addresses = [address1, address2]

If we add user1 to a Session, it will also addaddress1, address2 implicitly:

>>> sess = Session()
>>> sess.add(user1)
>>> address1 in sess
True

save-update cascade also affects attribute operations for objectsthat are already present in a Session. If we add a thirdobject, address3 to the user1.addresses collection, itbecomes part of the state of that Session:

>>> address3 = Address()
>>> user1.append(address3)
>>> address3 in sess
>>> True

save-update has the possibly surprising behavior which is thatpersistent objects which were removed from a collectionor in some cases a scalar attributemay also be pulled into the Session of a parent object; this isso that the flush process may handle that related object appropriately.This case can usually only arise if an object is removed from one Sessionand added to another:

>>> user1 = sess1.query(User).filter_by(id=1).first()
>>> address1 = user1.addresses[0]
>>> sess1.close()   # user1, address1 no longer associated with sess1
>>> user1.addresses.remove(address1)  # address1 no longer associated with user1
>>> sess2 = Session()
>>> sess2.add(user1)   # ... but it still gets added to the new session,
>>> address1 in sess2  # because it's still "pending" for flush
True

The save-update cascade is on by default, and is typically takenfor granted; it simplifies code by allowing a single call toSession.add() to register an entire structure of objects withinthat Session at once. While it can be disabled, thereis usually not a need to do so.

One case where save-update cascade does sometimes get in the way is in thatit takes place in both directions for bi-directional relationships, e.g.backrefs, meaning that the association of a child object with a particular parentcan have the effect of the parent object being implicitly associated with thatchild object’s Session; this pattern, as well as how to modify itsbehavior using the cascade_backrefs flag,is discussed in the section Controlling Cascade on Backrefs.

delete

The delete cascade indicates that when a “parent” objectis marked for deletion, its related “child” objects should also be markedfor deletion. If for example we have a relationship User.addresseswith 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 tworelated 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 havedelete cascade, SQLAlchemy’s default behavior is to instead de-associateaddress1 and address2 from user1 by setting their foreign keyreference 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 notdeleted, 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 withdelete-orphan cascade, which will emit a DELETE for the relatedrow if the “child” object is deassociated from the parent. The combinationof delete and delete-orphan cascade covers both situations whereSQLAlchemy has to decide between setting a foreign key column to NULL versusdeleting the row entirely.

ORM-level “delete” cascade vs. FOREIGN KEY level “ON DELETE” cascade

The behavior of SQLAlchemy’s “delete” cascade has a lot of overlap with theON DELETE CASCADE feature of a database foreign key, as wellas with that of the ON DELETE SET NULL foreign key setting when “delete”cascade is not specified. Database level “ON DELETE” cascades are specific to the“FOREIGN KEY” construct of the relational database; SQLAlchemy allowsconfiguration of these schema-level constructs at the DDL levelusing options on ForeignKeyConstraint which are describedat ON UPDATE and ON DELETE.

It is important to note the differences between the ORM and the relationaldatabase’s notion of “cascade” as well as how they integrate:

• A database level ON DELETE cascade is configured effectivelyon the many-to-one side of the relationship; that is, we configureit relative to the FOREIGN KEY constraint that is the “many” sideof a relationship. At the ORM level, this direction is reversed.SQLAlchemy handles the deletion of “child” objects relative to a“parent” from the “parent” side, which means that delete anddelete-orphan cascade are configured on the one-to-manyside.

• Database level foreign keys with no ON DELETE settingare often used to prevent a parentrow from being removed, as it would necessarily leave an unhandledrelated row present. If this behavior is desired in a one-to-manyrelationship, SQLAlchemy’s default behavior of setting a foreign keyto NULL can be caught in one of two ways:

• The easiest and most common is just to set theforeign-key-holding column to NOT NULL at the database schemalevel. An attempt by SQLAlchemy to set the column to NULL willfail with a simple NOT NULL constraint exception.

• The other, more special case way is to set the passive_deletesflag to the string "all". This has the effect of entirelydisabling SQLAlchemy’s behavior of setting the foreign key columnto NULL, and a DELETE will be emitted for the parent row withoutany affect on the child row, even if the child row is presentin memory. This may be desirable in the case whendatabase-level foreign key triggers, either special ON DELETE settingsor otherwise, need to be activated in all cases when a parent row is deleted.

• Database level ON DELETE cascade is vastly more efficientthan that of SQLAlchemy. The database can chain a series of cascadeoperations across many relationships at once; e.g. if row A is deleted,all the related rows in table B can be deleted, and all the C rows relatedto each of those B rows, and on and on, all within the scope of a singleDELETE statement. SQLAlchemy on the other hand, in order to supportthe cascading delete operation fully, has to individually load eachrelated collection in order to target all rows that then may have furtherrelated collections. That is, SQLAlchemy isn’t sophisticated enoughto emit a DELETE for all those related rows at once within this context.

• SQLAlchemy doesn’t need to be this sophisticated, as we instead providesmooth integration with the database’s own ON DELETE functionality,by using the passive_deletes option in conjunctionwith properly configured foreign key constraints. Under this behavior,SQLAlchemy only emits DELETE for those rows that are already locallypresent in the Session; for any collections that are unloaded,it leaves them to the database to handle, rather than emitting a SELECTfor them. The section Using Passive Deletes provides an example of this use.

• While database-level ON DELETE functionality works only on the “many”side of a relationship, SQLAlchemy’s “delete” cascadehas limited ability to operate in the reverse direction as well,meaning it can be configured on the “many” side to delete an objecton the “one” side when the reference on the “many” side is deleted. Howeverthis can easily result in constraint violations if there are other objectsreferring to this “one” side from the “many”, so it typically is onlyuseful when a relationship is in fact a “one to one”. Thesingle_parent flag should be used to establishan in-Python assertion for this case.

When using a relationship() that also includes a many-to-manytable using the secondary option, SQLAlchemy’sdelete cascade handles the rows in this many-to-many table automatically.Just like, as described in Deleting Rows from the Many to Many Table,the addition or removal of an object from a many-to-many collectionresults in the INSERT or DELETE of a row in the many-to-many table,the delete cascade, when activated as the result of a parent objectdelete operation, will DELETE not just the row in the “child” table but alsoin the many-to-many table.

delete-orphan

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

delete-orphan cascade implies that each child object can onlyhave one parent at a time, so is configured in the vast majority of caseson a one-to-many relationship. Setting it on a many-to-one ormany-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 objectis associated with only one parent at a time.

merge

merge cascade indicates that the Session.merge()operation should be propagated from a parent that’s the subjectof the Session.merge() call down to referred objects.This cascade is also on by default.

refresh-expire

refresh-expire is an uncommon option, indicating that theSession.expire() operation should be propagated from a parentdown to referred objects. When using Session.refresh(),the referred objects are expired only, but not actually refreshed.

expunge

expunge cascade indicates that when the parent object is removedfrom the Session using Session.expunge(), theoperation should be propagated down to referred objects.

Controlling Cascade on Backrefs

The save-update cascade by default takes place on attribute change eventsemitted from backrefs. This is probably a confusing statement moreeasily described through demonstration; it means that, given a mapping such as this:

mapper(Order, order_table, properties={
    'items' : relationship(Item, backref='order')
})

If an Order is already in the session, and is assigned to the orderattribute of an Item, the backref appends the Item to the itemscollection of that Order, resulting in the save-update cascade takingplace:

>>> o1 = Order()
>>> session.add(o1)
>>> o1 in session
True
 
>>> i1 = Item()
>>> i1.order = o1
>>> i1 in o1.items
True
>>> i1 in session
True

This behavior can be disabled using the cascade_backrefs flag:

mapper(Order, order_table, properties={
    'items' : relationship(Item, backref='order',
                                cascade_backrefs=False)
})

So above, the assignment of i1.order = o1 will append i1 to the itemscollection of o1, but will not add i1 to the session. You can, ofcourse, add() i1 to the session at a later point. Thisoption may be helpful for situations where an object needs to be kept out of asession until it’s construction is completed, but still needs to be givenassociations to objects which are already persistent in the target session.