python - 如何使用 ForeignKeyConstraint 和复合外键与 SQLAlchemy 级联删除？

Question

我目前正在努力在一组表上设置 SQLalchemy 级联删除，其中一个表引用为双复合主键作为外键。这是一些要理解的代码示例：

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



Base = declarative_base()

class Dad(Base):
    __tablename__ = "dad"
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="dad", passive_deletes=True)

class Mom(Base):
    __tablename__ = "mom"
    id = Column(Integer, primary_key=True)
    children = relationship("Child", back_populates="mom", passive_deletes=True)

class Child(Base):
    __tablename__ = "child"
    dad_id = Column(Integer, ForeignKey("dad.id", ondelete="CASCADE"), primary_key=True)
    mom_id = Column(Integer, ForeignKey("mom.id", ondelete="CASCADE"), primary_key=True)

    dad = relationship("Dad", back_populates="children")
    mom = relationship("Mom", back_populates="children")
    toys = relationship("Toy", back_populates="child", passive_deletes=True)

class Toy(Base):
    __tablename__ = "toy"
    id = Column(Integer, primary_key=True)
    dad_id = Column(Integer, nullable=False)
    mom_id = Column(Integer, nullable=False)
    child = relationship("Child", back_populates="toys")

    @declared_attr
    def __table_args__(cls):
        return (
            ForeignKeyConstraint(
                ["dad_id", "mom_id"],
                ["child.dad_id", "child.mom_id"],
                ondelete="CASCADE", onupdate="CASCADE"
            ),
        )

我的问题是，当我实例化爸爸、妈妈、孩子和一些玩具时，我无法级联删除孩子（例如）。我收到以下错误：

sqlalchemy.exc.IntegrityError：（psycopg2.errors.ForeignKeyViolation）更新或删除表“child”违反了表“toy”上的外键约束“toy_dad_id_mom_id_fkey”

详细信息：键 (dad_id, mom_id)=(1, 1) 仍然从表“toy”中引用。

标签： pythonsqlalchemyforeign-keys