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 > <https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship.params.cascade>) > and that seems to ignore the fk cascade and instead attempts to set the > foreign keys to NULL. Next, as per this SO discussion > <https://stackoverflow.com/questions/5033547/sqlalchemy-cascade-delete> I > added `passive_deletes=True` and next `passive_deletes="all"` (docs > <https://docs.sqlalchemy.org/en/13/orm/relationship_api.html#sqlalchemy.orm.relationship.params.passive_deletes>) > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/c9e846e5-ba6e-43b5-bbb1-34a1f612ab60o%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/c9e846e5-ba6e-43b5-bbb1-34a1f612ab60o%40googlegroups.com?utm_medium=email&utm_source=footer>. -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2081b721-4f09-48da-99a9-4211fbaae93f%40www.fastmail.com.
