postgresql 未使用级联删除sqlalchemy

htzpubme  于 2023-01-13  发布在  PostgreSQL
关注(0)|答案(1)|浏览(166)

当我尝试从parent表中删除所有条目时,出现以下错误
sqlalchemy.exc.IntegrityError:(psycopg2.errors.ForeignKeyViolation)对表"父"的更新或删除违反了表"子"的外键约束"子_p_id_fkey"详细信息:键(id)=(1)仍然从表"子"引用。
这是我的代码,我已经尝试过按照SQLAlchemy: cascade deleterelationship的两端添加cascade,在这种情况下,我得到的是
sqlalchemy.exc.ArgumentError:对于多对一关系"子.父",通常只在一对多关系的"一"端配置删除孤立级联,而不在多对一或多对多关系的"多"端配置。要强制此关系允许特定"父"对象通过"子.父"关系一次仅由一个"子"对象引用,这将允许删除-孤立级联在此方向上发生,请设置single_parent = True标志。(此错误的背景信息位于:https://sqlalche.me/e/14/bbf0

from sqlalchemy import create_engine, Column, ForeignKey, Integer, delete
from sqlalchemy.orm import declarative_base, relationship, Session

Base = declarative_base()

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child",
        back_populates="parent",
        cascade="save-update, merge, delete, delete-orphan",
    )

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id))
    parent = relationship(Parent, back_populates="children")

engine = create_engine(
    "postgresql://user:pass@localhost:5432/postgres", future=True
)

Base.metadata.create_all(engine)

with Session(engine) as session, session.begin():
    foo = Parent()
    session.add(foo)
    session.add(Child(parent=foo))

with Session(engine) as session:
    session.execute(delete(Parent).where(Parent.id == 1))
wj8zmpe1

wj8zmpe11#

据我所知,当你直接使用delete这样的东西时,你就超出了orm的范围。要执行关系级联,你需要使用session.delete(foo)

ORM删除

所以在最后一个代码块中,你需要做一些类似这样的事情来触发relationshipParent上的级联:

with Session(engine) as session:
    # Select the parent back from the db
    foo = session.get(Parent, 1)
    # Now delete it with the ORM
    session.delete(foo)

批量删除/核心删除

要在使用session.execute(delete(Parent).where(Parent.id == 1))时进行级联,您需要在外键上设置ondelete='CASCADE',如下所示:

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    p_id = Column(Integer, ForeignKey(Parent.id, ondelete='CASCADE'))
    parent = relationship(Parent, back_populates="children")

relationship(..., cascade="...")ondelete=...组合在一起的说明如下:
删除时使用外键与范式级联关系

相关问题