Sqlalchemy: Cascade Delete

SQLAlchemy: cascade delete

The problem is that sqlalchemy considers Child as the parent, because that is where you defined your relationship (it doesn't care that you called it "Child" of course).

If you define the relationship on the Parent class instead, it will work:

children = relationship("Child", cascade="all,delete", backref="parent")

(note "Child" as a string: this is allowed when using the declarative style, so that you are able to refer to a class that is not yet defined)

You might want to add delete-orphan as well (delete causes children to be deleted when the parent gets deleted, delete-orphan also deletes any children that were "removed" from the parent, even if the parent is not deleted)

EDIT: just found out: if you really want to define the relationship on the Child class, you can do so, but you will have to define the cascade on the backref (by creating the backref explicitly), like this:

parent = relationship(Parent, backref=backref("children", cascade="all,delete"))

(implying from sqlalchemy.orm import backref)

SQLAlchemy not cascade deleting multiple levels down

I came across a similar issue in our project, where we define cascades on the ORM level and also use lazy=dynamic relationships. This caused the cascade not to run on the bottom-most children.

Dynamic loading causes the relationship to return a Query object when accessed.

Delete on queries is quite limited, in order to increase performance, as documented here:
https://docs.sqlalchemy.org/en/13/orm/query.html

The method does not offer in-Python cascading of relationships - it
is assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any
foreign key references which require it, otherwise the database may
emit an integrity violation if foreign key references are being
enforced.

After the DELETE, dependent objects in the Session which were impacted
by an ON DELETE may not contain the current state, or may have been
deleted. This issue is resolved once the Session is expired, which
normally occurs upon Session.commit() or can be forced by using
Session.expire_all(). Accessing an expired object whose row has been
deleted
will invoke a SELECT to locate the row; when the row is not found,
an ObjectDeletedError is raised.

Therefore a solution for your problem could be either defining cascades on the database level, or using other types of relationships.

Related question was raised here: SQLAlchemy delete doesn't cascade

EDIT: (Solution I applied is changing the loading type on query level - in options)

SQLAlchemy delete doesn't cascade

You have the following...

db.session.query(User).filter(User.my_id==1).delete()

Note that after "filter", you are still returned a Query object. Therefore, when you call delete(), you are calling delete() on the Query object (not the User object). This means you are doing a bulk delete (albeit probably with just a single row being deleted)

The documentation for the Query.delete() method that you are using says...

The method does not offer in-Python cascading of relationships - it is
assumed that ON DELETE CASCADE/SET NULL/etc. is configured for any
foreign key references which require it, otherwise the database may
emit an integrity violation if foreign key references are being
enforced.

As it says, running delete in this manner will ignore the Python cascade rules that you've set up. You probably wanted to do something like..

user = db.session.query(User).filter(User.my_id==1).first()
db.session.delete(user)

Otherwise, you may wish to look at setting up the cascade for your database as well.

SQLAlchemy cascading deletes to many to many table

If you are using postgres, it would be useful to know wich version you are running. There are some similar issues which might be related if you are using a version lower than 12.2.

However, from your code, where did you try ondelete="CASCADE"?
This is working for me:

association = Table("association_table", 
   PkModel.metadata,
   Column("foo.id", ForeignKey("foos.id")),
   Column("value.id", ForeignKey("values.id", ondelete="CASCADE"))
   )

class Key(PkModel):
    __tablename__ = "keys" 
    values = relationship('Value', back_populates='key', cascade='all, delete-orphan')

class Value(PkModel):
    __tablename__ = "values"
    created = Column(DateTime, nullable=False, default=dt.utcnow)
    value = Column(Text, nullable=False)
    key = relationship("Key", back_populates="values")
    key_id = Column(ForeignKey("keys.id"))

class Foo(PkModel):
    __tablename__ = "foos"
    taglist = relationship(
       "Value", 
        order_by="desc(Value.created)", 
        secondary=association, 
        lazy='dynamic'
)

Then I can run (using postgres 12.2):

>>>f1 = models.Foo.create()
>>>f1.taglist.all()
[]
>>>k1 = models.Key.create(values=[models.Value(value="bar")])
>>>k1.values
[<Value 54a7c726-acc9...b8c87e590>]
>>>models.Value.query.all()
[<Value 54a7c726-acc9...b8c87e590>]

>>>f1.update(taglist=[v1])
<Foo 8a1b6701-384e-4bc0-8e29-9ac4e64a4fdf>
>>>f1 = models.Foo.query.all()[0]
>>>f1.taglist.all()
[<Value 54a7c726-acc9...b8c87e590>]

>>>k1.delete()
None
>>>f1.taglist.all()
[]

>>>models.Key.query.all()
[]
>>>models.Value.query.all()
[]
>>>models.Foo.query.all()
[<Foo 8a1b6701-384e-4...4e64a4fdf>]

I tried after without ondelete="CASCADE" and I could reproduce your error.

ForeignKeyViolation: update or delete on table "values" violates foreign key constraint "association_table_value.id_fkey" on table "association_table"

From your code, for future readers, note the following:

• Missing the __tablename__, I suppose from your code is set adding 's'.
• On the association table, missing a ) on Column("foo.id").
• Value does not have a mapped column named 'created'.

How to implement cascade deleting in SQLAlchemy Oracle?

The output from insp.get_foreign_keys("crm_post_attachments") shows that the actual table in the database does not match your ORM model. The model specifies

    post_id = Column(
        Integer, ForeignKey("crm_post.id", ondelete="CASCADE"), nullable=False
    )

but the reflected Foreign Key from the table shows

[
    {
        'name': 'sys_c00310238', 
        'constrained_columns': ['post_id'], 
        'referred_schema': None, 
        'referred_table': 'crm_post', 
        'referred_columns': ['id'], 
        'options': {}
    }
]

The empty options dict indicates that no cascade rule is in effect. passive_deletes=True is telling SQLAlchemy not to bother taking care of cascading the deletes because the backend will handle it.

In order to match your model the FK in the table should have 'options': {'ondelete': 'CASCADE'}. If you drop the FK and re-create it with that option, i.e.,

ALTER TABLE crm_post_attachments 
  ADD FOREIGN KEY(post_id) REFERENCES crm_post (id) ON DELETE CASCADE

then your code (with passive_deletes=True) should work.

---

Related Topics