How to implement cascade deleting in SQLAlchemy Oracle?-CodePudding

I'm doing an API service and trying to do cascade deleting for Attachments class. All the time I get integiry error ((cx_Oracle.IntegrityError) ORA-02292:)when trying to delete a post. I've tried backref/back_populates, played with cascade='all, delete-orphan' etc - the same error. DB is Oracle. Output from insp.get_foreign_keys("crm_post_attachments") is:

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

Here are the current models:

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)

CodePudding user response：

The output from insp.get_foreign_keys("crm_post_attachments") shows that the actual table in the database does not match your ORM model. The model specifies

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

but the reflected Foreign Key from the table shows

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

The empty options dict indicates that no cascade rule is in effect. passive_deletes=True is telling SQLAlchemy not to bother taking care of cascading the deletes because the backend will handle it.

In order to match your model the FK in the table should have 'options': {'ondelete': 'CASCADE'}. If you drop the FK and re-create it with that option, i.e.,

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

then your code (with passive_deletes=True) should work.