SQLAlchemyを使って１つのテーブル内で同じ外部キーを複数紐づける

概要

FastAPI で SQLAlchemy を使用してる際に「１つのテーブル内で同じ外部キーを複数持つ場合の models.py の記述方法」に手こずったので備忘録として残しておきます。

やりたいこと

以下のような ２ つのテーブルが存在していると仮定します。
このとき、「user テーブルの image_id1 と image_id2 の 2 つの id を image テーブルの id と紐づけた外部キー制約を設定したい」状況を考えます。

【image】

id	type
1	3
2	5
3	7
4	8

【user】

id	image_id1	image_id2
1	2	1
2	3	4
3	1	3
4	4	2

テーブルを作成するクエリは以下です。

create_table.sql

CREATE TABLE images (
    id INT NOT NULL,
    type INT NOT NULL,
    PRIMARY KEY (id),
    UNIQUE (id)
);

CREATE TABLE users (
    id INT NOT NULL,
    image_id1 INT NOT NULL,
    image_id2 INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (image_id1) REFERENCES images (id),
    FOREIGN KEY (image_id2) REFERENCES images (id)
);

成功パターン

models.py

from sqlalchemy import Column, Integer, ForeignKeyConstraint
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Images(Base):
    __tablename__ = "images"
    id = Column(Integer, nullable=False, unique=True)
    type = Column(Integer, nullable=False)
    users = relationship("Users", back_populates="images")

class Users(Base):
    __tablename__ = "users"
    __table_args__ = (
        ForeignKeyConstraint(
            ["image_id1", "image_id2"],
            ["images.id", "images.id"],
        ),
    )
    id = Column(Integer, nullable=False, unique=True)
    image_id1 = Column(Integer, nullable=False)
    image_id2 = Column(Integer, nullable=False)
    images = relationship("Images", back_populates="users")

ポイントは __table_args__ に ForeignKeyConstraint を指定する際に、image_id1 と image_id2 それぞれに images.id を紐づけてあげることです。

NGパターン

以下のパターンはどれも NG でした。

NGパターン１

...
image_id1 = Column(Integer, ForeignKey("images.id"),nullable=False)
image_id2 = Column(Integer, ForeignKey("images.id"),nullable=False)
...
images = relationship("Images", back_populates="users")
...

NGパターン2

...
image1 = relationship("Images", foreign_keys=[image_id1], back_populates="users")
image2 = relationship("Images", foreign_keys=[image_id2], back_populates="users")
...

NGパターン3

...
image = relationship("Images", foreign_keys=[image_id1, image_id2], back_populates="users")
...

NGパターン4

...
class Users(Base):
    __tablename__ = "users"
    __table_args__ = (
        ForeignKeyConstraint(
            ["image_id1"],
            ["images.id"],
        ),
        ForeignKeyConstraint(
            ["image_id2"],
            ["images.id"],
        ),
    )
...

教訓

複数のカラムにまたがる制約は __table_args__ に追加しましょう。