No unique constraint matching for relationship

Question

I have three tables with relations in my schema. At the top level there's the report which can contain many bids and many contacts. Also a contact can be a bidder to many bids.

I would like to have a one to many relationship between a report and contacts (this works and I am able to use a foreign key in contacts table to refer to the report id), a one to many relationship between a report and bids and one to many relationship between a contact and bids - these two fail with the same error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) there is no unique constraint matching given keys for referenced table "contacts" [SQL: '\nCREATE TABLE bids (\n\tparent_id1 CHAR(32) NOT NULL, \n\tparent_id2 CHAR(32) NOT NULL, \n\tPRIMARY KEY (parent_id1, parent_id2), \n\tFOREIGN KEY(parent_id1) REFERENCES contacts (dcis_factor_code), \n\tFOREIGN KEY(parent_id2) REFERENCES contacts (dcis_factor_cntct_code)\n)\n\n']

Here are simplified table definitions for bids and contacts that fails on execution with the above mentioned error:

class BidDetail(Base):
    __tablename__ = "bids"
    parent_id1 = Column(TEXT, ForeignKey("contacts.dcis_factor_code"),
                        primary_key=True)
    parent_id2 = Column(TEXT, ForeignKey("contacts.dcis_factor_cntct_code"),
                        primary_key=True)

class Contact(Base):
    __tablename__ = 'contacts'

    dcis_factor_code = Column(CHAR(32), primary_key=True)
    dcis_factor_cntct_code = Column(CHAR(32), primary_key=True)
    childs = relationship("BidDetail", lazy="dynamic")

I am not sure what am I doing wrong... Some help would be appreciated.

p.s. edited to show the full error.

Possible duplicate of [Relations on composite keys using sqlalchemy](https://stackoverflow.com/questions/7504753/relations-on-composite-keys-using-sqlalchemy) — univerio, Aug 04 '17 at 18:18
I will check if the linked answer solves my problem, but note that the error comes from Postgres, not from SQLAlchemy. — Ben Gosub, Aug 07 '17 at 10:15
Postgresql sees 2 separate foreign key constraints, as configured in SQLA vs. a multi column foreign key, each to a part of the composite primary key. The parts are not unique, their composite is. — Ilja Everilä, Aug 07 '17 at 10:18
The composite key solves my problem, how do I chose that question as the answer to this one? — Ben Gosub, Aug 08 '17 at 08:25

No unique constraint matching for relationship

0 Answers0