[sqlalchemy] Re: AmbiguousForeignKeysError
Never mind the OperationalError, I was missing inserting one of the
required foreign key fields.
The AmbiguousForeignKeysError was caused by missing foreign_keys in both
side of direction
and is resolved.
Thanks
On Sun, 31 May 2020 at 13:49, Sydo Luciani wrote:
> 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 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/CAJspodgK%3DyXOJggd033HiUmoVqRKZL3GqWD%2BrxdGH9LPK45tGQ%40mail.gmail.com.
[sqlalchemy] Re: AmbiguousForeignKeysError
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 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.
[sqlalchemy] Re: AmbiguousForeignKeysError
Correction on wordings:
One parent table, two child tables, one foreign key from each child
pointing to the same field in parent with "one to one relationship" works
with no problem, but getting "AmbiguousForeignKeysError" as soon as adding
the second foreign key to child tables pointing to the second field in
parent. 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_fields = 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_fields = 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.
Do I need to have 4 relationships or can be done with two relationships ?
Thank you
On Sat, 30 May 2020 at 18:13, Sydo Luciani 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),
>
