In SQLAlchemy, why does a Post with author_id and editor_id both referencing authors raise AmbiguousForeignKeysError, and how do you fix it?
answer
- joins are inferred from constraints
- two paths to one table
- raised at configuration, not definition
- name the column on each side
basics
~20 srelationship() infers its join from the foreign keys between two tables, and two keys to authors give two candidate paths. Pass foreign_keys= on every relationship involved, Post.author and Post.editor and their reverse collections, naming its own column.
solid answer
~40 s`relationship()` builds its join by finding the `ForeignKey` constraints between the two tables. With both `author_id` and `editor_id` pointing at `authors.id`, `Post.author` cannot tell which path is its own, so the first use of the mapping, or an explicit `configure_mappers()`, raises `AmbiguousForeignKeysError`; defining the class does not. The fix is `foreign_keys=[author_id]` on `Post.author` and `foreign_keys=[editor_id]` on `Post.editor`, and the reverse collections need it too: `Author.written` with `foreign_keys="Post.author_id"` and `Author.edited` with `"Post.editor_id"`, because `back_populates` does not copy the setting across. String forms are evaluated with `eval()`, so they must never carry user input. `primaryjoin=` is the general alternative when the join needs more than a column choice.
code
python · 47 linesfrom __future__ import annotations
from sqlalchemy import ForeignKey, String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship
class Base(DeclarativeBase):
pass
class Author(Base):
__tablename__ = "authors"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(100))
written: Mapped[list[Post]] = relationship(
back_populates="author", foreign_keys="Post.author_id"
)
edited: Mapped[list[Post]] = relationship(
back_populates="editor", foreign_keys="Post.editor_id"
)
class Post(Base):
__tablename__ = "posts"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(200))
author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
editor_id: Mapped[int | None] = mapped_column(ForeignKey("authors.id"))
author: Mapped[Author] = relationship(
back_populates="written", foreign_keys=[author_id]
)
editor: Mapped[Author | None] = relationship(
back_populates="edited", foreign_keys=[editor_id]
)
engine = create_engine("sqlite://")
Base.metadata.create_all(engine)
with Session(engine) as session, session.begin():
ada, lin = Author(name="Ada"), Author(name="Lin")
session.add(Post(title="Draft", author=ada, editor=lin))
with Session(engine) as session:
post = session.scalars(select(Post)).one()
print(post.author.name, post.editor.name)
print([p.title for p in post.editor.edited])go deeper
Recall the error name and its fix: foreign_keys= naming the column each relationship should use when two foreign keys point at the same table.
Explain how relationship() infers joins from ForeignKey constraints, why the error waits for mapper configuration, and why every relationship of each path needs the argument.
Make misconfiguration fail in CI by calling configure_mappers() in a test, and know when primaryjoin= is the right tool instead.
Set a convention for multi-path links, distinct relationship names and explicit foreign_keys on both sides, so reviewers catch a missing one before the first request does.
## How relationship() finds its join `relationship()` does not ask you for a join condition in the common case. When mappers are configured, it inspects the two tables and looks for `ForeignKey` constraints that connect them. One foreign key from `posts` to `authors` gives one obvious join, `authors.id = posts.author_id`, and from it SQLAlchemy also knows which column to fill at flush. Give `posts` a second key to the same table, as a blog does when a post has both an author and an editor, and that inference breaks: ```python class Post(Base): __tablename__ = "posts" id: Mapped[int] = mapped_column(primary_key=True) author_id: Mapped[int] = mapped_column(ForeignKey("authors.id")) editor_id: Mapped[int | None] = mapped_column(ForeignKey("authors.id")) author: Mapped[Author] = relationship() editor: Mapped[Author | None] = relationship() ``` Both `author` and `editor` see two foreign key paths and cannot tell which one is theirs. ## When the error fires Defining the class succeeds. The failure comes at **mapper configuration**, which SQLAlchemy runs lazily the first time a mapped class is used: constructing a `Post`, building a query against it, or calling `configure_mappers()` explicitly. The message names the relationship and the fix: ```plaintext sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Post.author - 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. ``` Two points follow from the timing: - An import-only smoke test does not catch it; a test that instantiates each class or calls `configure_mappers()` does. - `AmbiguousForeignKeysError` is a subclass of `ArgumentError`, the family of mapper-configuration mistakes, not an error from the database. ## The fix: foreign_keys on every relationship of each path `foreign_keys=` tells one relationship which column holds the reference. It goes on **each** relationship that touches both tables, the collection side included: ```python class Author(Base): __tablename__ = "authors" id: Mapped[int] = mapped_column(primary_key=True) written: Mapped[list[Post]] = relationship(back_populates="author", foreign_keys="Post.author_id") edited: Mapped[list[Post]] = relationship(back_populates="editor", foreign_keys="Post.editor_id") ``` and on `Post`, `author = relationship(back_populates="written", foreign_keys=[author_id])` with the matching `editor` line. The steps are: 1. List each foreign key path: here `author_id` and `editor_id`. 2. Give each path its own pair of relationships with distinct names (`written` and `author`, `edited` and `editor`). 3. Pass `foreign_keys` on all four. `back_populates` does **not** copy it across: leave it off `Author.written` and that relationship raises the same error. `foreign_keys` also decides persistence. When the flush inserts a new `Author` assigned as a post's editor, its generated primary key is copied into `editor_id`, not `author_id`. ## String forms and eval() Inside a class body the other class may not exist yet, so Declarative accepts strings such as `foreign_keys="Post.author_id"`. Two rules apply: - With a list, the brackets go **inside** the string: `foreign_keys="[Post.author_id]"`. - Declarative evaluates these strings with Python's `eval()`. They must come from source code, never from user input or configuration that a user can influence. ## Related messages | Message | Cause | Usual fix | |---|---|---| | `AmbiguousForeignKeysError` | more than one foreign key path between the tables | `foreign_keys=` on each relationship | | the same error mentioning "via secondary table" | a many-to-many link table with several keys to one side | `foreign_keys=` listing the link-table columns | | `NoForeignKeysError` | no `ForeignKey` between the tables at all | add the `ForeignKey`, or pass `primaryjoin=` | `primaryjoin=` is the more general tool: it replaces the whole join expression, for example to add a filter on a status column. For the two-keys case, `foreign_keys` is shorter and is what the error message asks for. ## When a second foreign key is added later The error usually appears not when a model is first written but when a second path is added to a working one. Adding `editor_id` makes the **existing** `Post.author` and `Author.posts` ambiguous too, even though nobody touched them. A checklist for that change: - Add the new column with its own `ForeignKey`. - Add `foreign_keys=` to every relationship between the two tables, old and new. - Give each path distinct relationship names on the one side, such as `written` and `edited`, and update the `back_populates` values to match. - Keep a test that imports every model and calls `configure_mappers()`, so the next added path fails in CI.
- Why did the tests pass while the first request in production raised AmbiguousForeignKeysError?Mapper configuration is lazy: it runs on the first use of a mapped class, not at import. If no test constructed a Post or queried it, the misconfiguration never surfaced. A test that calls `configure_mappers()` after importing every model fails fast on this and on other relationship configuration errors.
- When would you reach for primaryjoin= instead of foreign_keys=?When choosing a column is not enough, such as a relationship limited to published posts, or a join with no ForeignKey constraint in the schema. `primaryjoin=` replaces the whole join expression; `foreign_keys=` only tells SQLAlchemy which existing column to use, which is all the two-keys case needs.
- SQLAlchemy picks the first ForeignKey declared when two point at one table.
- The error fires when the class body is executed at import.
- foreign_keys is needed only on the many-to-one side; back_populates copies it.
- foreign_keys strings are safe to build from request data.
- Two foreign keys to one table require a secondary= table.