Thanks for your help Michael.
I've tried explicitly removing both parents and as you say it gets deleted,
I was just misunderstanding the semantics when you have multiple parents
and delete-orphan.
My other mistake was to assume that PrimaryKeyConstraint implied
nullable=False, as primary_key=True seems to. I've added nullable=False to
all my primary key columns now, that should help with debugging.
Joshua.
On Monday, 15 October 2012 06:22:42 UTC+1, Michael Bayer wrote:
>
>
> On Oct 14, 2012, at 2:47 PM, Joshua Leahy wrote:
>
> I've managed to create a situation where SQLAlchemy seems to generate
> queries that break foreign key constraints. As I'm not certain I'm not
> making a silly mistake I thought I'd post the example here so people could
> take a look rather than just reporting a bug.
>
> Basically I have a table that looks like this:
>
> class ReactionItem(Base):
> __tablename__ = 'reaction_items'
> __table_args__ = (
> PrimaryKeyConstraint('reaction_id', 'item_id'),
> )
> reaction_id = Column(Integer, ForeignKey('reactions.id'))
> item_id = Column(Integer, ForeignKey('items.id'))
>
> And SQLAlchemy generates a query that looks like this:
>
> INSERT INTO reaction_items (item_id) VALUES (?)
>
> That's not going to work because it doesn't specify all elements of the
> primary key. It doesn't cause SQLite to error out, but MySQL does refuse to
> commit.
>
> This is with the latest version of SQLAlchemy on PyPI and I've included a
> minimal example below.
>
> Any help would be much appreciated (flames would be most welcome as they'd
> confirm I'm just doing something ridiculous).
>
>
> There's a few ways this kind of thing can happen. The usual reason is
> because ReactionItem(item=item) would be flushed when you say
> "reaction.items", which lazyloads, then autoflushes, then flushes
> ReactionItem(item=item) without yet being attached to Reaction, since we
> haven't yet loaded reaction.items. Due to the specific order of operations
> in your example, this isn't *quite* happening, but I'd advise you create
> association objects such as ReactionItem as ReactionItem(item=item,
> reaction=reaction), or otherwise use the cascade_backrefs flag (see
> http://docs.sqlalchemy.org/en/latest/orm/session.html#backref-cascade).
>
> In this case, you still have an incomplete ReactionItem present because
> "reaction.items.remove(reaction.items[0])" de-associates ReactionItem from
> the parent Reaction. It then gets flushed, only associated with the
> Item, and there's no reaction_id to use, so you get the error.
>
> SQLAlchemy doesn't try to jump in on that because primary key columns are
> often autogenerating, or via triggers, so it doesn't assume that just
> because the PK is composite, it should guess that maybe you don't mean to
> be doing what you're doing. Because the database will report an error
> anyway, it avoids making a guess here. SQLite for some reason is not
> emitting an error when a NULL primary key is being created, so if you're
> using SQLite maybe put "nullable=False" on those PK columns.
>
> If the expectation here is that "delete-orphan" would take care of it,
> that is true, but the ReactionItem object has to be an orphan on all sides,
> which means detach it from Item as well:
>
> ri = reaction.items[0]
> reaction.items.remove(ri)
> ri.item = None
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/aOwrIfYIKssJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
