Composite Foreign Keys and Many-to-Many Relationships in SQLAlchemy

For lack of a better example, suppose you’re storing data about authors and articles and you have a many-to-many relationship between authors and articles. To keep things interesting, you’ve decided to use a composite primary key for your “author” table.

Since I couldn’t find an example of doing this online, here’s how I got it all to work:

from sqlalchemy import (create_engine, Column, String, ForeignKey,
                        ForeignKeyConstraint)
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()
Session = sessionmaker(bind=engine)


class Author(Base):
    __tablename__ = 'author'

    first_name = Column(String, primary_key=True)
    last_name = Column(String, primary_key=True)

    articles = relationship(
        'Article',
        secondary='authorarticle',
        back_populates='authors',
    )


class Article(Base):
    __tablename__ = 'article'
    slug = Column(String, primary_key=True)

    authors = relationship(
        'Author',
        secondary='authorarticle',
        back_populates='articles',
    )


class AuthorArticle(Base):
    __tablename__ = 'authorarticle'

    article_slug = Column(String, ForeignKey(Article.slug), primary_key=True)
    author_first = Column(String, primary_key=True)
    author_last = Column(String, primary_key=True)

    __table_args__ = (
        ForeignKeyConstraint(
            ['author_first', 'author_last'],
            ['author.first_name', 'author.last_name'],
        ),
    )


if __name__ == '__main__':
    Base.metadata.create_all(engine)
    session = Session()

    author = Author(
        first_name='Name',
        last_name='Surname'
    )
    article = Article(slug='some-article')

    author.articles.append(article)

    session.add(author)
    session.add(article)
    session.commit()

Notes

It’s crucial you use ForeignKeyConstraint so that SQLAlchemy knows it should use a composite foreign key in your association table. If you define the author’s first and last name columns as separate foreign keys, you’ll see an error like:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Author.articles - there are multiple foreign key paths linking the tables via secondary table ‘authorarticle’. Specify the ‘foreign_keys’ argument, providing a list of those columns which should be counted as containing a foreign key reference from the secondary table to each of the parent and child tables.

Now if you read that and see “Specify the ‘foreign_keys’ argument”, you might give that a try, but it’ll never work until you’ve defined the composite foreign key properly.

However, suppose you’re like me and you do try define the ‘foreign_keys’ argument, but then figure out the composite foreign key thing; you might have ended up with a misconfigured ‘foreign_keys’ that looks like this:

# in the Article class
# XXX: this is the wrong way of doing it
articles = relationship(...,
    foreign_keys=(
        '[AuthorArticle.author_first, '
        ' AuthorArticle.author_last]'
    ),
)

This’ll get you the following error:

sqlalchemy.exc.NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Author.articles - there are no foreign keys linking these tables via secondary table ‘authorarticle’. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify ‘primaryjoin’ and ‘secondaryjoin’ expressions.

If you do decide to define the foreign keys on the relationship, you’ll have to provide all the foreign keys, like the following:

# the right way
articles = relationship(...,
    foreign_keys=(
        '[AuthorArticle.article_slug, '  # crucial
        ' AuthorArticle.author_first, '
        ' AuthorArticle.author_last]'
    ),
)

You’ll need to do that for the relationship() call in both Article and Author.