On Dec 1, 2009, at 2:16 PM, rajasekhar911 wrote:
> i have following relation b/w 3 tables
>
> class A(DeclarativeBase)
> __tablename__='a'
> id=Column(Unicode(50))#primarykey
> name=Column(Unicode(50))
> cc=relation(C, \
> primaryjoin=id == C.b_id,\
> foreign_keys=[C.b_id],\
> uselist=False,cascade='all, delete, delete-
> orphan')
>
> class B(DeclarativeBase)
> __tablename__='b'
> id=Column(Unicode(50))#primarykey
> name=Column(Unicode(50))
>
>
> class C(DeclarativeBase)
> __tablename__='c'
> id=Column(Integer)#primarykey
> name=Column(Unicode(50))
> b_id=Column(Unicode(50),ForeignKey('b.id',\
> onupdate="CASCADE",ondelete="CASCADE"))
>
> bb=B(name='bb')
> aa=A(name='aa')
> aa.cc=C(name='ca',b_id=bb.id)
you haven't established a relation() between C and B here so SQLA has no
awareness that B needs to be inserted before C. Also bb.id is None until the
session is flushed. If you use a relation() from C to B, that would solve the
issue. Also A.cc is strange here in that you're repurposing the foreign key
C.b_id to point to A which is nonsensical to be on A, which has nothing to do
with B.
> DBSession.add(bb)
> DBSession.add(aa)
>
> throws
>
> (IntegrityError) (1452, Cannot add or update a child row: a foreign
> key constraint fails (`test/c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY
> (`b_id`) REFERENCES `b` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) )
> u INSERT INTO c (id, b_id, name) VALUES (%s, %s, %s)
>
> if i do a flush in b/w the add it works fine
> DBSession.add(bb)
> DBSession.flush()
> DBSession.add(aa)
>
> even
> bb=B(name='bb')
> cc=C(name='ca',b_id=bb.id)--removed the relation from B
> DBSession.add(bb)
> DBSession.add(cc)
> throws the same error
>
> I am working on turbogears 2.0, SQLAlchemy0.5.6
> session is created with autoflush=true and autocommit=true
> mysql 5.0 with innodb engine
>
> shouldn't the flush takes place by default or the transaction should
> take care of it?
>
> --
>
> 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.
>
>
--
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.
