如何在SQLAlchemy Oracle中实现级联删除?

Question

我正在做一个API服务，并试图做级联删除附件类。一直以来，我得到完整性错误((cx_Oracle.IntegrityError) ORA-02292:)，当试图删除一个帖子。我试过backref/back_populates，玩cascade='all, delete-orphan'等-同样的错误。DB为Oracle。insp.get_foreign_keys("crm_post_attachments")的输出为:

[{'name': 'sys_c00310238', 'constrained_columns': ['post_id'], 'referred_schema': None, 'referred_table': 'crm_post', 'referred_columns': ['id'], 'options': {}}]

以下是当前的模型:

class Post(Base):
__tablename__ = "crm_post"
id = Column(Integer, primary_key=True, index=True)
title = Column(String(255), nullable=False)
text = Column(String)
img = Column(LargeBinary)
author_id = Column(Integer, ForeignKey("crm_user.id"), nullable=False)
sdate = Column(DateTime)
edate = Column(DateTime)
post_type = Column(Integer, ForeignKey("crm_dir_post_types.id"), nullable=False)
attachments = relationship("PostAttachments", back_populates="post", passive_deletes=True, cascade='all, delete-orphan')

class PostAttachments(Base):
__tablename__ = "crm_post_attachments"
id = Column(Integer, primary_key=True, index=True)
attachment = Column(LargeBinary)
post_id = Column(Integer, ForeignKey("crm_post.id", ondelete='CASCADE'), nullable=False)
post = relationship("Post", back_populates="attachments", passive_deletes=True)

Answer 1

insp.get_foreign_keys("crm_post_attachments")的输出显示数据库中的实际表与您的ORM模型不匹配。模型指定

post_id = Column(
Integer, ForeignKey("crm_post.id", ondelete="CASCADE"), nullable=False
)

，但是表中反射的外键显示

[
{
'name': 'sys_c00310238', 
'constrained_columns': ['post_id'], 
'referred_schema': None, 
'referred_table': 'crm_post', 
'referred_columns': ['id'], 
'options': {}
}
]

空的optionsdict表示没有生效的级联规则。passive_deletes=True告诉SQLAlchemy不要费心处理级联删除，因为后端会处理它。

为了匹配您的模型，表中的FK应该具有'options': {'ondelete': 'CASCADE'}。如果您删除FK并使用该选项重新创建它，即

ALTER TABLE crm_post_attachments 
ADD FOREIGN KEY(post_id) REFERENCES crm_post (id) ON DELETE CASCADE

那么你的代码(使用passive_deletes=True)应该可以工作。