relation with delete-orphan cascade behaves incorrectly

[Former user]

Hi, i have read the FAQ and i understand that updating a key used in a relation will not and should not update the relation property, but i believe the delete-orphan cascade should take the key into account.

An example is provided, just in case.

[Mike Bayer]

check out this FAQ entry. It doesn't exactly answer your question but the gist is, setting b.foo_id to a number means nothing to SQLAlchemy as far as object relations.

http://www.sqlalchemy.org/trac/wiki/FAQ#Isetthefoo_idattributeonmyinstanceto7butthefooattributeisstillNone-shouldntithaveloadedFoowithid7

[Mike Bayer]

just noticed that you read the FAQ. Sorry ! To answer your comment that "the cascade should take the key into account", that's exactly what the FAQ talks about - an object assignment event in response to a foreign key identifier change. The entry explains why that is problematic and will likely remain unsupported.

[Mike Bayer]

ill also add that the real source of your problem here is that the delete-orphan cascade is checking up front on a pending object. We didn't always have this behavior, it used to be only on the remove event that delete-orphan would take place. An option to allow "pending orphans" to be flushed again is a potential solution to this. though im not sure i like the idea of providing more obscure options.

[Former user]

i don't know about the internals and from the way you talk about it it sounds like there is no sane way around populating the relation, i was thinking about having the orphan check look not only at the relation but also at the id(s) on the relation if the relation is not set.

That would, imo, also comply with expected behaviour and no surprises. But again, i have no clue if that makes sense in the big picture

[Mike Bayer]

we don't hang any rules off of foreign key attributes, basically. the ORM looks at them in an indirect way - a population rule, driven by the primary join condition, puts data into them during flush, the primary join condition joins to it during a load. We make no other assumptions about it or what it means. That is how SQLA is so open ended about joining to other tables - we assume as little as possible about how other tables are joined.

A primary join condition could have all kinds of columns in it - when those get populated, what action do we take ? In your example, nothing has been flushed yet - how do we know b.a_id = a.id even means anything ? what if other conditions need to be met in order for b.a_relation to be "non-empty" ? how do we find what the value of "b.a_relation" is, when nothing has yet been flushed ?

The cascade rule drives exclusively off of the relation(), not any of the foreign keys related to its persistence. It also can be used for one-to-many, m2m, or many-to-one, so its not just a single local FK that the rule would need to drive off of to be consistent.

It would be a complicating factor to have the cascade rule drive off of two different things at different times, and would also introduce inconsistency to the ORM's current approach of total agnosticism towards foreign key attributes.

The real issue here is that our decision to make delete-orphan aggressively check its condition on pending objects makes life more difficult in a case like this, when you'd like to flush foreign key attributes only and get the relations populated on a refresh.