SQLite Forum

How should RETURNING work with a cascading delete?
Login
(Speaking for myself only:)

The cascading delete case you present is an outlier. In the usual case, cascaded deletions will happen in an arbitrary set of tables, which only by coincidence includes the origination of the cascade. I see no reason that it should be made easier to get the cascaded results only for that one somewhat special case when so many other cascaded results are unavailable that way. Most foreign keys reference **other** tables. (I suspect that is why they are named "FOREIGN".)  Of course, this is just reiterating your point, "Shouldn't all tables be treated the same?" Yes, it is best that they are, to ease human conceptualization.

The consequence, "you might get something in between depending on the delete order and the timing", gives me a shuddering dread. SQL is best considered as a way of specifying set operations.  That "delete order" and "timing" dependence is a monkey wrench thrown into that eminently useful conceptualization.