postgresql 使用多个外键

efzxgjgh  于 2023-01-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(155)

我有一个带有复合主键的表,这两列然后被用作另一个表的主键和外键,但我似乎无法在sqlalchemy中创建关系。
我收到以下错误

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Parent.children - there are multiple foreign key paths linking the tables.  Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

这是我的代码,我如何修复它?

from sqlalchemy import create_engine, Column, ForeignKey, Integer
from sqlalchemy.orm import declarative_base, relationship

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id_1 = Column(Integer, primary_key=True, unique=True)
    id_2 = Column(Integer, primary_key=True, unique=True)
    children = relationship("Child", back_populates="parent", foreign_keys='[Child.f_id_1,Child.f_id_2]')

class Child(Base):
    __tablename__ = "child"
    f_id_1 = Column(Integer, ForeignKey(Parent.id_1), primary_key=True)
    f_id_2 = Column(Integer, ForeignKey(Parent.id_2), primary_key=True)
    parent = relationship(Parent, back_populates="children", foreign_keys='[Child.f_id_1,Child.f_id_2]')

engine = create_engine("postgresql://user:pass@localhost:5432", future=True)

Base.metadata.create_all(engine)

from sqlalchemy.orm import Session

with Session(engine) as session:
    session.add(Parent())
ulydmbyx

ulydmbyx1#

看起来您需要提供一个ForeignKeyConstraint,否则SqlAlchemy将无法理解值应该配对在一起。

class Child(Base):
    __tablename__ = "child"
    f_id_1 = Column(Integer, primary_key=True)
    f_id_2 = Column(Integer, primary_key=True)

    __table_args__ = (
        ForeignKeyConstraint([f_id_1, f_id_2], [Parent.id_1, Parent.id_2]),
        {},
    )

相关问题