How to create a foreign key in a table which points to a composite primary key of another table ?

[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.model3_id")
    model2: "Model2" = Relationship(back_populates="model1")

class Model2(SQLModel, table=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)

Description

I have a 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 want to create a foreign key in Model1 which points to the composite primary key (model2.model3_id, model2.model4_id). What is the syntax to do that ? The source code seems to not accept it. Is it a missing feature ? I can try a pull request if it's the case.

I tried to make the foreign key point just to model2.model3_id but it fails with the 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 (model3_id)
)

]

which makes completely sense, since model2.model3_id can be repeated. The right thing would be to point to the whole primary key (model2.model3_id, model2.model4_id).

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:

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9.5

Additional Context

No response

[joaopfg]

Solved ! The solution is to use SQLAlchemy internals inside the SQLModel model class. The code became:

from typing import Optional, List

from sqlalchemy import ForeignKeyConstraint

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


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


class Model1(SQLModel, table=True):
    __table_args__ = (
        ForeignKeyConstraint(
            ['model2_id1', 'model2_id2'],
            ['model2.model3_id', 'model2.model4_id'],
        ),
    )

    id: Optional[int] = Field(default=None, primary_key=True)

    model2_id1: Optional[int] = Field(default=None)
    model2_id2: Optional[int] = Field(default=None)
    model2: "Model2" = Relationship(back_populates="model1")

class Model2(SQLModel, table=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)

However, it would be good to have this syntax available directly in SQLModel. I will try to look into that and maybe create a pull request