I got it working with adding additional foreign_keys.
one inside backref for one direction, and one outside of backref for the
other direction.
so we need foreign_keys in both directions if using bidirectional
relationship.
Now I am able to insert into parent table, but getting sql error inserting
to child.
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) foreign key
mismatch child_one referencing parent.
On Sat, 30 May 2020 at 18:13, Sydo Luciani <sydo.luci...@gmail.com> wrote:
>
> One parent table, two child tables, two foreign keys pointing to a field
> in parent with "one to one relationship" works with no problem, but getting
> "AmbiguousForeignKeysError" as soon as adding the second foreignkey to
> child table. tried various combinations but none has worked so far.
> specifically tring to add foreign_keys as suggested in error message.
>
> Here is the code that throwing error.
>
> class Parent(Base):
> __tablename__ = 'parent'
>
> field_one = Column(String(256),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(128),
> nullable=False,
> primary_key=True)
>
> p_child_one_field_one = relationship("ChildOne",
> uselist=False,
> passive_deletes=True,
> backref=backref("ref_to_parent_field_one",
> foreign_keys="[ChildOne.field_one,
> ChildOne.field_two]"),
> cascade="all, delete-orphan")
>
> p_child_two_field_one = relationship("ChildTwo",
> uselist=False,
> passive_deletes=True,
> backref=backref("ref_to_parent_field_two",
> foreign_keys="[ChildTwo.field_one,
> ChildTwo.field_two]"),
> cascade="all, delete-orphan")
>
>
>
> class ChildOne(Base):
> __tablename__ = 'child_one'
>
> field_one = Column(String(256),
> ForeignKey('parent.field_one',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(256),
> ForeignKey('parent.field_two',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
>
> class ChildTwo(Base):
> __tablename__ = 'child_two'
>
> field_one = Column(String(256),
> ForeignKey('parent.field_one',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
> field_two = Column(String(256),
> ForeignKey('parent.field_two',
> onupdate="CASCADE",
> ondelete='CASCADE'),
> unique=True,
> nullable=False,
> primary_key=True)
>
>
> Any suggestion to fix the problem will be appreciated.
>
> Thank you
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CAJspodjUV%2B73hcrzUDvyOY11dzK6frb2sS%2B9p1Ny8so8NVHGTw%40mail.gmail.com.
