Re: [sqlalchemy] Befuddled with FK, ondelete, and delete cascade

[Mike Bayer]

in the backref you have to tell it that the ORM should expect child rows to be deleted, and also that FK cascade setting in the database will accommodate the operation, using cascade="all, delete-orphan" as well as passive_deletes=True.

the docs for this are at https://docs.sqlalchemy.org/en/13/orm/collections.html#passive-deletes  and it has an example.

There are also the main cascade docs on the subject at https://docs.sqlalchemy.org/en/13/orm/cascades.html#delete .   however, as I am looking at it, this is too wordy and the examples are not canonical use as well as that there is no cross linking to the above section, so I will update all of this right now because I dont know how people can understand these docs right now.

On Thu, Jul 23, 2020, at 1:56 AM, Jens Troeger wrote:

Hello,

I have a parent and a child class defined like so:

# Parent table.
class User(Base):
    __tablename__ = "users"
    id = Column(Integer)

# Child table.
class Stat(Base):
    __tablename__ = "stats"
    id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    user_id = Column(Integer)

    # Useful ORM relationship.
    user = relationship("User", backref=backref("stats"))

When I deleted a user, I got the following error:

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1048, "Column 'user_id' cannot be null")

[SQL: UPDATE stats SET user_id=%(user_id)s WHERE stats.id = %(stats_id)s]

[parameters: {'user_id': None, 'stats_id': '13'}]

Good, because I don’t want orphaned stats when the user is being deleted and the `nullable=False` for the `stats.id` column caught the issue.

However, that means that the foreign key cascade didn’t work. From the docs it looks like the default cascade for a relationship is "safe-update, merge" (link) and that seems to ignore the fk cascade and instead attempts to set the foreign keys to NULL. Next, as per this SO discussion I added `passive_deletes=True` and next `passive_deletes="all"` (docs) in the hopes that the foreign key cascade works — to no avail.

When I the set the parameter `cascade="all, delete-orphan"` on the backref() then deleting worked. However, that’s on the ORM level and still avoids the foreign key cascade.

What am I missing here? How do I get the foreign key cascade to work?

Much thanks,

Jens

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/c9e846e5-ba6e-43b5-bbb1-34a1f612ab60o%40googlegroups.com.