Self-referencial relationships do not update FK value upon delete

[Former user]

(original reporter: ged) If you have a relationship to another table, deleting a record in the table which is the target of a many-to-one relationship issues an update of all records which pointed to that record. If the relationship is self referencial, this doesn't happen, leading to an incoherent DB on SQLite (since it doesn't enforce FK constraints). I guess other DBMS would refuse the delete operation altogether, which is not what is desired here (but for those DBMS, one can use passive_deletes).

Attached test case demonstrate the difference in behavior between self-referencial and normal relationships.

PS: I don't want to cascade delete, I want to emulate a "ON DELETE SET NULL", which is what you get on non self-referencial relationships.

[Mike Bayer]

the first observation here is that the notion that a delete() will update many to ones which reference the object is incorrect. No such traversal to "potential parents" is performed. The address.user_id is updated to NULL as a result of the one-to-many "addresses" collection. the same thing occurs for "children" but there is a sorting issue causing the behavior.

the topological sort and/or the execution stream through that sort is not functioning correctly regarding the point at which the "user_id" column gets set to NULL. It is actually happening within the flush(), but doesn't get persisted so the change is lost.

[Mike Bayer]

99d3e251cf6f24807138ef03cdd0f7c55b00e810

[Former user]

(original author: ged) Thanks for the awesome response time, as usual!

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)