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 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.
