SQLAlchemy deleting parent neither deleting child or setting child ForeignKey value to null

Question

I have various one to many relationships in SQLAlchemy. When I delete a Parent entry, I want its associated children to also be deleted. I have tried using the cascade property following the docs. However, not only are Children not being deleted, but they are also not having their parent id set to null, as I was expecting after reading this question and other SQLAlchemy discussions.

Here are my models:

class Parent(Base):
    __tablename__ = 'parent'
    __table_args__ = {'extend_existing': True}

    id = Column(Integer, primary_key = True)

    children = relationship("Child", order_by=Child.id, backref="parent", cascade="all, delete-orphan")

    def save_to_db(self):
        db_session.add(self)
        db_session.commit()

class Child(Base):
    __tablename__ = 'child'
    __table_args__ = {'extend_existing': True}

    id = Column(Integer, primary_key = True)
    parent_id = Column(Integer, ForeignKey('parent.id')

And here is my delete endpoint:

    parent = Parent.find_by_id(id)
    deleted = delete(Parent.__table__, Parent.__table__.c.id==id)
    db_session.execute(deleted)
    db_session.commit()

When I first create a Parent with a Child:

event_info = EventInfo.find_by_id(id)
ids = []
for event in event_info.events:
    ids.append(event.id)
print(str(id) + " Has: "+str(ids)) #prints: 1 has [1]

But when I delete that Parent, and then create another Parent with another Child:

    event_info = EventInfo.find_by_id(id)
    ids = []
    for event in event_info.events:
        ids.append(event.id)
    print(str(id) + " Has: "+str(ids)) #prints: 1 has [1,2]. When I print the information of 1, it is the information of the Child created with the original (now deleted) parent.

So, when deleting Parents and then creating new ones, Children just add up and I end up with Parents with Children from many old, now deleted Parents.

Any ideas? I'm sure I have my relationships configured wrong in some way, or I am going about deleting things wrong, but I can't find any inconsistencies with the documentation.

Ilja Everilä · Accepted Answer · 2018-07-15T19:45:22.900

The problem is that

deleted = delete(Parent.__table__, Parent.__table__.c.id==id)
db_session.execute(deleted)

is a core bulk operation, though it deletes a single row. The session is totally oblivious to the query's effects, so the ORM cascades don't apply. If you want to use the ORM cascades, use the session to delete the entity:

parent = Parent.find_by_id(id)
db_session.delete(parent)

Also since you're using SQLite, and if you have not enabled foreign key constraint checks, the database allows removing parent rows referenced by children. You should follow the instructions from the documentation, if you need foreign key constraint checks. The idea is to emit

PRAGMA foreign_keys=ON

for new connections. If you wish that deletes cascade, you could add the same behaviour in the database as well:

class Child(Base):
    ...
    parent_id = Column(Integer, ForeignKey('parent.id', ondelete='CASCADE'))

Sometimes you may want to let the database handle cascading entirely, especially if a parent entity has not yet populated the "children" relationship, in which case you can read about passive deletes in SQLAlchemy.

SQLAlchemy deleting parent neither deleting child or setting child ForeignKey value to null

1 Answers1