There is no unique constraint matching given keys (one-to-many, connecting with many-to-many tables)

[Jafetlch]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from typing import List, Optional
from sqlmodel import Field, Relationship, SQLMode

class Model1(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    model2: List["Model2"] = Relationship(back_populates="model1")

class Model2_Model3(SQLModel, table=True):
    model2: Optional[int] = Field(default=None, foreign_key="model2.id", primary_key=True)
    model3: Optional[int] = Field(default=None, foreign_key="model3.id", primary_key=True)

class Model2(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    model1_id: Optional[int] = Field(default=None, foreign_key="model1.id", primary_key=True)

    model1: Model1 = Relationship(back_populates="model2")
    model3: List["Model3"] = Relationship(back_populates="model2", link_model=Model2_Model3)

class Model3(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

    model2: Model2 = Relationship(back_populates="model3", link_model=Model2_Model3)

Description

I have this error trying this code.

cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "model2"

The main idea is to connect one to many with model1 and model2. And model2 with model3 using many to many. But i try different methods, so, I need help

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.4

Additional Context

    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "model2"

[SQL:
CREATE TABLE model2_model3 (
        model2 INTEGER NOT NULL,
        model3 INTEGER NOT NULL,
        PRIMARY KEY (model2, model3),
        FOREIGN KEY(model2) REFERENCES model2 (id),
        FOREIGN KEY(model3) REFERENCES model3 (id)
)

]

[meirdev]

The problem is here:

class Model2(SQLModel, table=True):
    ...
    model1_id: Optional[int] = Field(default=None, foreign_key="model1.id", primary_key=True)

You need to remove primary_key=True from this line.

[Jafetlch]

Thanks @meirdev