如何用SQLAlchemy实现可关联Cat或Dog表的联合外键？

我需要给review表添加一个字段，使其能够关联cats或dogs任意一张表的主键。了解到PostgreSQL可以通过非SQL标准的CHECK约束实现类似效果，但想知道是否能通过SQLAlchemy实现该功能，哪怕部分数据库原生不支持。

以下是我的代码示例：

schemas.py #

from dataclasses import dataclass

@dataclass
class Cat: ...

@dataclass
class Dog: ...

@dataclass
class review:
    stars_value: int
    entity: Cat | Dog
    comment: str

models.py #

from sqlalchemy import Column, Table, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

cats = Table(
    "cats",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String)
)

dogs = Table(
    "dogs",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
)

# 这里的ForeignKey写法无效
review = Table(
    "review",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("comment", String),
    Column("entity", Integer, ForeignKey("cats.id" | "dogs.id"))  # 语法错误
)

如果上述方式无法实现，我计划通过中间表来完成：

review_categories = Table(
    "review_categories",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("cat", Integer),
    Column("dog", Integer),
)

解决方案 #

1. 基于CHECK约束的"多态外键"（支持CHECK的数据库）

SQLAlchemy可以直接定义CheckConstraint来模拟这种关联逻辑，适合PostgreSQL、SQLite等支持CHECK约束的数据库。

修改review表的定义，增加类型标识字段和约束：

from sqlalchemy import CheckConstraint

review = Table(
    "review",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("comment", String),
    Column("entity_id", Integer, nullable=False),
    Column("entity_type", String, nullable=False),
    # 约束：类型为cat时，id必须存在于cats表；类型为dog时，id必须存在于dogs表
    CheckConstraint(
        "(entity_type = 'cat' AND entity_id IN (SELECT id FROM cats)) "
        "OR (entity_type = 'dog' AND entity_id IN (SELECT id FROM dogs))",
        name="review_entity_check"
    )
)

如果使用SQLAlchemy声明式模型而非纯Table对象，还可以通过hybrid_property封装实体关联逻辑，方便直接获取对应的Cat或Dog实例。

2. 通用兼容方案（适配所有数据库）

如果需要兼容不支持CHECK约束的数据库（比如旧版MySQL），可以给review表添加两个独立的外键字段，并通过约束确保仅能有一个字段非空：

review = Table(
    "review",
    Base.metadata,
    Column("id", Integer, primary_key=True),
    Column("comment", String),
    Column("cat_id", Integer, ForeignKey("cats.id")),
    Column("dog_id", Integer, ForeignKey("dogs.id")),
    # 确保至少关联一个实体
    CheckConstraint("cat_id IS NOT NULL OR dog_id IS NOT NULL", name="review_entity_not_null"),
    # 确保最多关联一个实体
    CheckConstraint("NOT (cat_id IS NOT NULL AND dog_id IS NOT NULL)", name="review_entity_single")
)

这种方式不依赖数据库高级特性，且能通过外键保证数据完整性。在Python代码中，可以添加property来返回当前关联的实体：

# 若使用声明式模型
class Review(Base):
    __tablename__ = "review"
    id = Column(Integer, primary_key=True)
    comment = Column(String)
    cat_id = Column(Integer, ForeignKey("cats.id"))
    dog_id = Column(Integer, ForeignKey("dogs.id"))
    cat = relationship("Cat")
    dog = relationship("Dog")

    @property
    def entity(self):
        return self.cat if self.cat_id else self.dog

3. 关于中间表方案的优化

你提到的中间表review_categories可以实现关联，但需要额外维护关联关系，且无法直接通过外键保证数据唯一性和完整性。相比之下，直接在review表添加双外键+约束的方案更简洁，也更符合关系数据库的设计规范。

内容的提问来源于stack exchange，提问作者salius