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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/c9e846e5-ba6e-43b5-bbb1-34a1f612ab60o%40googlegroups.com.
