Recently I spent two days figuring out why a seemingly trivial use of a cascading delete relationship in SQLAlchemy did not work properly, eventually I found the answer in this StackOverflow post. Because it was a real puzzler I've created this short writing, perhaps it can be of help to someone facing the same problem someday.
The context of the problem is very simple: there are two tables (Parent
and Child
), each Parent
can only have one Child
and when a Parent
row is deleted the associated Child
row should also be deleted.
This is how that looks in SQLAlchemy Python code:
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
name = Column(String)
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
name = Column(String)
childID = Column(Integer, ForeignKey(Child.id))
child = relationship(Child, single_parent=True, cascade="all, delete, delete-orphan")
The following snippet shows how a parent and child row can be created in the database:
child = Child(id=456, name='a-child')
parent = Parent(id=123, name='a-parent', child=child)
session.add(parent)
session.commit()
So far, so good.
The problem with cascasing deletes not working as you would expect is when you use a bulk delete like this:
session.query(Parent).filter(Parent.id == 123).delete()
Even though the filtered query only returns at most one object, the delete is still considered a bulk operation and those operations do not cascade through ORM relationships.
In this case the proper way to perform a cascading delete is to get the Parent
object and perform a delete
on that:
parent = session.query(Parent).filter(Parent.id == 123).first()
session.delete(parent)
See the attached sqlalchemy-cascade-delete.py file for a reproducable example of this problem.