Using sqlalchemy.orm I am trying to link two tables on a composite key, but keep getting an error. Unfortunately, the official docs provide an example that uses a single primary key (not composite), so I tried to come up with a basic example that reproduces the issue:
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customer"
id = mapped_column(String, primary_key=True)
country = mapped_column(String, primary_key=True)
billing_address_id = mapped_column(Integer, ForeignKey("address.idx"))
shipping_address_id = mapped_column(Integer, ForeignKey("address.idx"))
billing_address = relationship("Address", foreign_keys=[billing_address_id])
shipping_address = relationship("Address", foreign_keys=[shipping_address_id])
class Address(Base):
__tablename__ = "address"
idx = mapped_column(Integer, primary_key=True)
address = mapped_column(String)
customer_id = mapped_column(ForeignKey("customer.id"))
customer_country = mapped_column(ForeignKey("customer.country"))
customers_using_this_adress = relationship(
"Customer", foreign_keys=[customer_id, customer_country]
)
# trying to define a customer triggers an error
c = Customer(id="A", country="B")
I get the following error:
AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Address.customers_using_this_adress - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.
Here’s the full traceback:
---------------------------------------------------------------------------
AmbiguousForeignKeysError Traceback (most recent call last)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py:2421, in JoinCondition._determine_joins(self)
2420 if self.primaryjoin_initial is None:
-> 2421 self.primaryjoin = join_condition(
2422 self.parent_persist_selectable,
2423 self.child_persist_selectable,
2424 a_subset=self.parent_local_selectable,
2425 consider_as_foreign_keys=consider_as_foreign_keys,
2426 )
2427 else:
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/sql/util.py:123, in join_condition(a, b, a_subset, consider_as_foreign_keys)
101 """Create a join condition between two tables or selectables.
102
103 e.g.::
(...)
121
122 """
--> 123 return Join._join_condition(
124 a,
125 b,
126 a_subset=a_subset,
127 consider_as_foreign_keys=consider_as_foreign_keys,
128 )
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/sql/selectable.py:1356, in Join._join_condition(cls, a, b, a_subset, consider_as_foreign_keys)
1355 if len(constraints) > 1:
-> 1356 cls._joincond_trim_constraints(
1357 a, b, constraints, consider_as_foreign_keys
1358 )
1360 if len(constraints) == 0:
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/sql/selectable.py:1501, in Join._joincond_trim_constraints(cls, a, b, constraints, consider_as_foreign_keys)
1500 if len(constraints) != 1:
-> 1501 raise exc.AmbiguousForeignKeysError(
1502 "Can't determine join between '%s' and '%s'; "
1503 "tables have more than one foreign key "
1504 "constraint relationship between them. "
1505 "Please specify the 'onclause' of this "
1506 "join explicitly." % (a.description, b.description)
1507 )
AmbiguousForeignKeysError: Can't determine join between 'address' and 'customer'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.
The above exception was the direct cause of the following exception:
AmbiguousForeignKeysError Traceback (most recent call last)
Cell In[11], line 32
26 customer_country = mapped_column(ForeignKey("customer.country"))
27 customers_using_this_adress = relationship(
28 "Customer", foreign_keys=[customer_id, customer_country]
29 )
---> 32 c = Customer(id="A", country="B")
File <string>:4, in __init__(self, **kwargs)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/state.py:570, in InstanceState._initialize_instance(*mixed, **kwargs)
567 self, instance, args = mixed[0], mixed[1], mixed[2:] # noqa
568 manager = self.manager
--> 570 manager.dispatch.init(self, args, kwargs)
572 try:
573 manager.original_init(*mixed[1:], **kwargs)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/event/attr.py:487, in _CompoundListener.__call__(self, *args, **kw)
485 fn(*args, **kw)
486 for fn in self.listeners:
--> 487 fn(*args, **kw)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py:4308, in _event_on_init(state, args, kwargs)
4306 instrumenting_mapper = state.manager.mapper
4307 if instrumenting_mapper:
-> 4308 instrumenting_mapper._check_configure()
4309 if instrumenting_mapper._set_polymorphic_identity:
4310 instrumenting_mapper._set_polymorphic_identity(state)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py:2374, in Mapper._check_configure(self)
2366 @util.langhelpers.tag_method_for_warnings(
2367 "This warning originated from the `configure_mappers()` process, "
2368 "which was invoked automatically in response to a user-initiated "
(...)
2371 )
2372 def _check_configure(self) -> None:
2373 if self.registry._new_mappers:
-> 2374 _configure_registries({self.registry}, cascade=True)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py:4116, in _configure_registries(registries, cascade)
4110 Mapper.dispatch._for_class(Mapper).before_configured() # type: ignore # noqa: E501
4111 # initialize properties on all mappers
4112 # note that _mapper_registry is unordered, which
4113 # may randomly conceal/reveal issues related to
4114 # the order of mapper compilation
-> 4116 _do_configure_registries(registries, cascade)
4117 finally:
4118 _already_compiling = False
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py:4158, in _do_configure_registries(registries, cascade)
4156 if not mapper.configured:
4157 try:
-> 4158 mapper._post_configure_properties()
4159 mapper._expire_memoizations()
4160 mapper.dispatch.mapper_configured(mapper, mapper.class_)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/mapper.py:2391, in Mapper._post_configure_properties(self)
2388 self._log("initialize prop %s", key)
2390 if prop.parent is self and not prop._configure_started:
-> 2391 prop.init()
2393 if prop._configure_finished:
2394 prop.post_instrument_class(self)
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/interfaces.py:544, in MapperProperty.init(self)
537 """Called after all mappers are created to assemble
538 relationships between mappers and perform other post-mapper-creation
539 initialization steps.
540
541
542 """
543 self._configure_started = True
--> 544 self.do_init()
545 self._configure_finished = True
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py:1634, in RelationshipProperty.do_init(self)
1632 self._setup_entity()
1633 self._setup_registry_dependencies()
-> 1634 self._setup_join_conditions()
1635 self._check_cascade_settings(self._cascade)
1636 self._post_init()
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py:1881, in RelationshipProperty._setup_join_conditions(self)
1880 def _setup_join_conditions(self) -> None:
-> 1881 self._join_condition = jc = JoinCondition(
1882 parent_persist_selectable=self.parent.persist_selectable,
1883 child_persist_selectable=self.entity.persist_selectable,
1884 parent_local_selectable=self.parent.local_table,
1885 child_local_selectable=self.entity.local_table,
1886 primaryjoin=self._init_args.primaryjoin.resolved,
1887 secondary=self._init_args.secondary.resolved,
1888 secondaryjoin=self._init_args.secondaryjoin.resolved,
1889 parent_equivalents=self.parent._equivalent_columns,
1890 child_equivalents=self.mapper._equivalent_columns,
1891 consider_as_foreign_keys=self._user_defined_foreign_keys,
1892 local_remote_pairs=self.local_remote_pairs,
1893 remote_side=self.remote_side,
1894 self_referential=self._is_self_referential,
1895 prop=self,
1896 support_sync=not self.viewonly,
1897 can_be_synced_fn=self._columns_are_mapped,
1898 )
1899 self.primaryjoin = jc.primaryjoin
1900 self.secondaryjoin = jc.secondaryjoin
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py:2308, in JoinCondition.__init__(self, parent_persist_selectable, child_persist_selectable, parent_local_selectable, child_local_selectable, primaryjoin, secondary, secondaryjoin, parent_equivalents, child_equivalents, consider_as_foreign_keys, local_remote_pairs, remote_side, self_referential, prop, support_sync, can_be_synced_fn)
2305 self.support_sync = support_sync
2306 self.can_be_synced_fn = can_be_synced_fn
-> 2308 self._determine_joins()
2309 assert self.primaryjoin is not None
2311 self._sanitize_joins()
File ~/miniconda3/envs/excel/lib/python3.11/site-packages/sqlalchemy/orm/relationships.py:2465, in JoinCondition._determine_joins(self)
2453 raise sa_exc.AmbiguousForeignKeysError(
2454 "Could not determine join "
2455 "condition between parent/child tables on "
(...)
2462 "parent and child tables." % (self.prop, self.secondary)
2463 ) from afe
2464 else:
-> 2465 raise sa_exc.AmbiguousForeignKeysError(
2466 "Could not determine join "
2467 "condition between parent/child tables on "
2468 "relationship %s - there are multiple foreign key "
2469 "paths linking the tables. Specify the "
2470 "'foreign_keys' argument, providing a list of those "
2471 "columns which should be counted as containing a "
2472 "foreign key reference to the parent table." % self.prop
2473 ) from afe
AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Address.customers_using_this_adress - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.
>Solution :
Unlike a composite primary key where you can add primary_key=True to columns you want, composite foreign keys do not work that way. What you have right now is two different foreign keys and not a single composite foreign key. You need to use ForeignKeyConstraint in the __table_args__ tuple like the following.
__table_args__ = (
ForeignKeyConstraint(
[customer_id, customer_country], [Customer.id, Customer.country]
),
)
Full code
from sqlalchemy import Column, ForeignKey, Integer, String, ForeignKeyConstraint
from sqlalchemy.orm import DeclarativeBase, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Customer(Base):
__tablename__ = "customer"
id = mapped_column(String, primary_key=True)
country = mapped_column(String, primary_key=True)
billing_address_id = mapped_column(Integer, ForeignKey("address.idx"))
shipping_address_id = mapped_column(Integer, ForeignKey("address.idx"))
billing_address = relationship("Address", foreign_keys=[billing_address_id])
shipping_address = relationship("Address", foreign_keys=[shipping_address_id])
class Address(Base):
__tablename__ = "address"
idx = mapped_column(Integer, primary_key=True)
address = mapped_column(String)
customer_id = mapped_column(Integer)
customer_country = mapped_column(Integer)
customers_using_this_adress = relationship(
"Customer", foreign_keys=[customer_id, customer_country]
)
__table_args__ = (
ForeignKeyConstraint(
[customer_id, customer_country], [Customer.id, Customer.country]
),
)
c = Customer(id="A", country="B")