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?

define ondelete="CASCADE" on the ForeignKey column,
define passive_deletes=True on the same relationships,
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.

`passive_deletes` seems to be the correct option. Having multiple relationships isn't bad form, and are often necessary to model the situation at hand (and seems like a good fit for your need). Initial guess is that all the relationships might need them, since it might otherwise force setting the reference to NULL before the database gets the chance to cascade — MatsLindh, Nov 27 '18 at 21:37
Please include your relationship configurations, as they are pertinent to the issue at hand. — Ilja Everilä, Nov 28 '18 at 05:05

score 1 · Accepted Answer · answered Nov 29 '18 at 19:19

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.

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

1 Answers1