TechOverflow Logo
TechOverflow

sqlmodel: How to fix NoForeignKeysError: Could not determine join condition between parent/child tables on relationship

Problem

When running your Python script using sqlmodel with model definition code such as

sqlmodel_example.py
from typing import List, Optional
from sqlmodel import ForeignKey, Relationship, SQLModel, Field
from sqlalchemy import create_engine
from sqlmodel import SQLModel

class Manufacturer(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(description="Manufacturer name")
    products: List["Product"] = Relationship(
        back_populates="manufacturer"
    )

class Product(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    mpn: Optional[str] = Field(description="Manufacturer part number", index=True)
    manufacturer: Optional[Manufacturer] = Relationship(
        back_populates="products"
    )

you see an error message such as

sqlmodel_nofk_example.sh
Traceback (most recent call last):
  File "/home/uli/Nextcloud/Experimental/InventreeLCSC/TestModel.py", line 25, in <module>
    product: Product = Product(mpn="123456")
                           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<string>", line 4, in __init__
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/state.py", line 566, in _initialize_instance
    manager.dispatch.init(self, args, kwargs)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/event/attr.py", line 497, in __call__
    fn(*args, **kw)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4396, in _event_on_init
    instrumenting_mapper._check_configure()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2388, in _check_configure
    _configure_registries({self.registry}, cascade=True)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4204, in _configure_registries
    _do_configure_registries(registries, cascade)
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 4245, in _do_configure_registries
    mapper._post_configure_properties()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/mapper.py", line 2405, in _post_configure_properties
    prop.init()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/interfaces.py", line 584, in init
    self.do_init()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1644, in do_init
    self._setup_join_conditions()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 1884, in _setup_join_conditions
    self._join_condition = jc = JoinCondition(
                                ^^^^^^^^^^^^^^
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2310, in __init__
    self._determine_joins()
  File "/home/uli/.local/lib/python3.12/site-packages/sqlalchemy/orm/relationships.py", line 2444, in _determine_joins
    raise sa_exc.NoForeignKeysError(
sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Manufacturer.products - there are no foreign keys linking these tables.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

Solution

When you encounter this error, the solution is simple: Explicitly specify the ForeignKey relationship by adding:

example.py
sa_relationship=ForeignKey("product.id")

as an argument to both sides of the Relationship field definition.

Full example:

example.py
from typing import List, Optional
from sqlmodel import ForeignKey, Relationship, SQLModel, Field
from sqlalchemy import create_engine
from sqlmodel import SQLModel

class Manufacturer(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(description="Manufacturer name")
    products: List["Product"] = Relationship(
        back_populates="manufacturer",
        sa_relationship=ForeignKey("product.id")
    )

class Product(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    mpn: Optional[str] = Field(description="Manufacturer part number", index=True)
    manufacturer: Optional[Manufacturer] = Relationship(
        back_populates="products",
        sa_relationship=ForeignKey("manufacturer.id")
    )
Check out similar posts by category: SQLModel, Python, Database