SQLAlchemy 引发完整性错误， "update or delete on table violates foreign key constraint"

Question

我正在尝试在连接关系上设置级联删除。但是，每当我尝试删除应用程序中的帖子时，我都会收到一条错误消息，指出“表“帖子”上的更新或删除违反了外键约束”，这是错误消息的照片:

这是我的代码:

class Post(db.Model):
    """Blog posts"""

    __tablename__ = "posts"

    id = db.Column(db.Integer,
                   primary_key=True,
                   autoincrement=True)
    title = db.Column(db.String(25),
                      nullable=False)
    content = db.Column(db.String(500),
                        nullable=False)
    created_at = db.Column(db.DateTime,
                           default=db.func.current_timestamp())
    user_table = db.Column(db.Integer, 
                           db.ForeignKey('users.id',
                                         ondelete='CASCADE'))

    tags = db.relationship('Tag', 
                            secondary="post_tags", 
                            back_populates="posts",
                            cascade="all, delete"
                            )


class Tag(db.Model):

    __tablename__ = "tags"

    id = db.Column(db.Integer,
                       primary_key=True,
                       autoincrement=True)
    name = db.Column(db.String,
                        unique=True)
    posts = db.relationship('Post',
                            secondary="post_tags",
                            back_populates="tags")
    
 

class Post_Tag(db.Model):

    __tablename__ = "post_tags"

    post_id = db.Column(db.Integer,
                        db.ForeignKey('posts.id'), primary_key=True)
    
    tag_id = db.Column(db.Integer,
                        db.ForeignKey('tags.id'), primary_key=True)

根据我查看过的文档和其他问题，我的设置似乎正确。我在这里做错了什么？

更新我可以删除标签，但无法删除帖子

Answer 1

您可能会收到此错误，因为您使用的是 backref 而不是 back_populates...

此外，我建议双向定义您的关系，即在父 Post 和子 Tag 中> 型号。这允许根据删除的对象使用不同的规则对辅助表进行级联删除。

对模型进行以下更改应该可以修复您收到的错误:

# Modify your tags relationship to the following:
class Post(db.Model):
    ...
    tags = db.relationship(
               'Tag', 
               secondary="post_tags", 
               back_populates="posts", # use back-populates instead of backref
               cascade="all, delete"
           )


# Also, define your relationship from your tag model
class Tag(db.Model):
    __tablename__ = "tags"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, unique=True)
    posts = db.relationship(
               'Post', 
               secondary="post_tags",
               back_populates="tags", # use back-populates instead of backref 
               # When a parent ("post") is deleted, don't delete the tags...
               passive_deletes=True
           )