skip to content

In SQLAlchemy, why does a Post with author_id and editor_id both referencing authors raise AmbiguousForeignKeysError, and how do you fix it?

level: middleimportance: should knowfreq 35%

answer

  1. joins are inferred from constraints
  2. two paths to one table
  3. raised at configuration, not definition
  4. name the column on each side

basics

~20 s

relationship() 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 lines
python
from __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

for a junior

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.

for a middle

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.

for a senior

Make misconfiguration fail in CI by calling configure_mappers() in a test, and know when primaryjoin= is the right tool instead.

for a principal

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.