How to make a foreign key of a table be the primary key of another table that has a composite primary key ?

[joaopfg]

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 Optional, List

from sqlmodel import SQLModel, Field, Relationship, create_engine, Session


engine = create_engine("postgresql://username:password@localhost:5432/db_dynamic")


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

    model2_id: Optional[int] = Field(default=None, foreign_key="model2.id")
    model2: "Model2" = Relationship(back_populates="model1")

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

    model3_id: Optional[int] = Field(default=None, foreign_key="model3.id", primary_key=True)
    model4_id: Optional[int] = Field(default=None, foreign_key="model4.id", primary_key=True)

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

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

    model4: List["Model4"] = Relationship(back_populates="model3", link_model=Model2)


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

    model3: List[Model3] = Relationship(back_populates="model4", link_model=Model2)

if __name__ == "__main__":
    SQLModel.metadata.create_all(engine)

    with Session(engine) as session:
        model3 = Model3()
        model4 = Model4()

        session.add(model3)
        session.add(model4)
        session.commit()

        model2 = Model2(**{"model3_id": 1, "model4_id": 1})

        session.add(model2)
        session.commit()

        model1 = Model1(**{"model2_id": 1})
        session.add(model1)
        session.commit()

Description

I have link table represented by Model2, which establishes a many to many relation between Model3 and Model4.

Now I want to establish a many to one relation between Model1 and the link table. To do that, I created a third primary key id in Model2 and a foreign key in Model1 which points to the id of Model2.

I'm using a PostgreSQL database created using a docker-compose.yml as follows:

version: '3.7'

services:
  db:
    image: postgres:13.0-alpine
    volumes:
      - postgres_data:/var/lib/postgresql/data/
    environment:
      - POSTGRES_USER=username
      - POSTGRES_PASSWORD=password
      - POSTGRES_DB=db_dynamic
    ports:
      - "5432:5432"

volumes:
  postgres_data:

However, I'm getting the following error:

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

[SQL: 
CREATE TABLE model1 (
	id SERIAL, 
	model2_id INTEGER, 
	PRIMARY KEY (id), 
	FOREIGN KEY(model2_id) REFERENCES model2 (id)
)

However, it works well with a SQLite database. The problem seems to be in how SQLModel (or SQLAlchemy) generates the schema for the PostgreSQL database.

Someone knows how to solve it ?

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9.5

Additional Context

No response