How do I define SQLAlchemy FKs and relationships to allow the database to cascade delete?

Question

How does one define a ForeignKey and relationship such that one can disable SQLAlchemy's FK-nullifying behavior? The documentation here seems to describe the use of passive_deletes=True to allow the database to cascade delete, but only in the context of defining the cascade relationship property documented here, a property which it seems to me defines how SQLAlchemy will perform the cascade deletion itself, which is explicitly described as slower than the database engine's cascade deletion in this section (see the green box titled ORM-level “delete” cascade vs. FOREIGN KEY level “ON DELETE” cascade).

To use the database's cascade delete, are we supposed to do the following?

1. define ondelete="CASCADE" on the ForeignKey column,
2. define passive_deletes=True on the same relationships,
3. AND define a cascade="delete, delete-orphan" parameter on all relationships between the objects?

It is step 3 that I seem to be confused about: it seems to be defining the cascade for SQLAlchemy rather than allowing the database to perform it's own deletion. But SQLAlchemy seems to want to null out all dependent foreign keys before the database can get a chance to cascade delete. I need to disable this behavior, but passive_deletes=True seems not to do it on its own.

The (late) answer here explicitly addresses my issue, but it is not working. He states

There's an important caveat here. Notice how I have a relationship specified with passive_deletes=True? If you don't have that, the entire thing will not work. This is because by default when you delete a parent record SqlAlchemy does something really weird. It sets the foreign keys of all child rows to NULL. So if you delete a row from parent_table where id = 5, then it will basically execute UPDATE child_table SET parent_id = NULL WHERE parent_id = 5

In my code

class Annotation(SearchableMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    locked = db.Column(db.Boolean, index=True, default=False)
    active = db.Column(db.Boolean, default=True)

    HEAD = db.relationship("Edit",
            primaryjoin="and_(Edit.current==True,"
            "Edit.annotation_id==Annotation.id)", uselist=False,
            lazy="joined", passive_deletes=True)

    edits = db.relationship("Edit",
            primaryjoin="and_(Edit.annotation_id==Annotation.id,"
                "Edit.approved==True)", lazy="joined", passive_deletes=True)
    history = db.relationship("Edit",
            primaryjoin="and_(Edit.annotation_id==Annotation.id,"
                "Edit.approved==True)", lazy="dynamic", passive_deletes=True)
    all_edits = db.relationship("Edit",
            primaryjoin="Edit.annotation_id==Annotation.id", lazy="dynamic",
            passive_deletes=True)

class Edit(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    edit_num = db.Column(db.Integer, default=0)
    approved = db.Column(db.Boolean, default=False, index=True)
    rejected = db.Column(db.Boolean, default=False, index=True)
    annotation_id = db.Column(db.Integer,
            db.ForeignKey("annotation.id", ondelete="CASCADE"), index=True)
    hash_id = db.Column(db.String(40), index=True)
    current = db.Column(db.Boolean, default=False, index=True, passive_deletes=True)

    annotation = db.relationship("Annotation", foreign_keys=[annotation_id])
    previous = db.relationship("Edit",
            primaryjoin="and_(remote(Edit.annotation_id)==foreign(Edit.annotation_id),"
            "remote(Edit.edit_num)==foreign(Edit.edit_num-1))")
    priors = db.relationship("Edit",
            primaryjoin="and_(remote(Edit.annotation_id)==foreign(Edit.annotation_id),"
            "remote(Edit.edit_num)<=foreign(Edit.edit_num-1))",
            uselist=True, passive_deletes=True)

simply setting passive_deletes=True on the parent relationship is not working. I also thought perhaps it was being caused by the relationship from the child to it's siblings (the relationships Edit.previous and Edit.priors) but setting passive_deletes=True on those two relationships does not solve the problem, and it causes the following warnings when I simply run an Edit.query.get(n):

/home/malan/projects/icc/icc/venv/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py:1790: SAWarning: On Edit.previous, 'passive_deletes' is normally configured on one-to-many, one-to-one, many-to-many relationships only. % self)

/home/malan/projects/icc/icc/venv/lib/python3.7/site-packages/sqlalchemy/orm/relationships.py:1790: SAWarning: On Edit.priors, 'passive_deletes' is normally configured on one-to-many, one-to-one, many-to-many relationships only. % self)

I have actually found this interesting question from 2015 that has never had an answer. It details a failed attempt to execute documentation code.

Answer 1

The passive_delete=True parameter tells SQLAlchemy not to load automatically related child objects into the session before deleting their parent, and therefore not to perform cascading actions on them. But if child objects are already loaded into the session, then SQLAlchemy will make a DELETE or UPDATE request for each, depending on the value of the cascade parameter. By default (passive_delete=False), child objects would be loaded with a separate SELECT request.

That said, there are still complications I'm working out; for instance, on a many-to-many table the id's aren't even nullifying. Possible next question.

By default, SQLAlchemy deletes records from the associative table when deleting related objects. To change the values of foreign keys to Null, we must use the parameter passive_deletes="all" for relation() and ondelete="SET NULL" for ForeignKey():

association_table = Table(
    "association",
    Base.metadata,
    Column("parents_id", Integer, ForeignKey("parents.id", ondelete="SET NULL")),
    Column("children_id", Integer, ForeignKey("children.id", ondelete="SET NULL")),
)

class Parent(Base):
    __tablename__ = "parents"

    id = Column(Integer, primary_key=True)
    children = relation(
        "Child",
        backref=backref("parents", passive_deletes="all"),
        secondary=association_table,
        passive_deletes="all",
    )

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True)

passive_deletes="all" tells SQLAlchemy not to process related objects when deleting a parent object ondelete="SET NULL" adds ON DELETE CASCADE clause in the foreign key definition in database schema

Answer 2

It seems that after a thorough attempt to analyze my relationships, I have discovered the problem.

First, I will note, passive_deletes=True is the only necessary parameter. You do not need to define cascade at all to take advantage of the database's cascade system.

More importantly, my problem seems to have stemmed from my tree of foreign-key depedencies. I had a cascade that looked like this:

       Annotation
      /     |    \ 
   Vote    Edit   annotation_followers
           /  \ 
    EditVote   tags

Where ondelete="CASCADE" was defined for each parent_id column on each child class. Until I set passive_deletes on all of the children in the graph, the nullification behavior continued to misbehave.

For anyone running into a similar problem, my advice is: thoroughly analyze all of your intersecting relationships, and define passive_deletes=True on all of them that it makes sense.

That said, there are still complications I'm working out; for instance, on a many-to-many table the id's aren't even nullifying. Possible next question.