Question: 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. 

Answer: 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")