SQLAlchemy中CheckConstraint未按预期生效，请求排查

我在SQLite上使用这个Entity模型已有一段时间，此前CheckConstraint仅包含前两行时运行正常。我希望新增第三行约束场景：当published为false且parent_id为空时，不对title施加约束，但插入请求仍触发完整性错误。

模型代码 #

class Entity(db.Model): 
    __tablename__ = 'entities'

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    parent_id = Column(Integer, ForeignKey('entities.id'))
    published = Column(Boolean, nullable=False) # determines if draft
    artwork_path = Column(String) # can't be empty if no parent id
    title = Column(Text) # must be empty if has parent id
    body = Column(Text) # can't be empty if has parent id
    created_at = Column(TIMESTAMP, server_default=func.now())
    last_updated = Column(TIMESTAMP, onupdate=func.now())

    __table_args__ = (
        CheckConstraint(
            "(parent_id IS NULL AND title IS NOT NULL AND artwork_path IS NOT NULL) OR \ " 
            "(parent_id IS NOT NULL AND title IS NULL AND body IS NOT NULL) OR \ "
            "(published = false AND parent_id IS NULL AND artwork_path IS NOT NULL)",
            name="check_post_or_comment"
        ),
    )

错误信息 #

sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) CHECK constraint failed: check_post_or_comment
[SQL: INSERT INTO entities (user_id, parent_id, published, artwork_path, title, body, last_updated) VALUES (?, ?, ?, ?, ?, ?, ?) RETURNING id, created_at]
[parameters: (1, None, 0, '5897187d9ac149b2bc4450432f0f4b98.jpg', None, None, None)]

问题原因 #

直接在CheckConstraint的字符串中使用published = false，SQLite无法正确识别这个布尔值比较逻辑：SQLite将布尔类型存储为数值0/1，而字符串中的false会被当作文本常量，和插入时的0（对应Python的False）不匹配，导致第三个约束条件始终不成立，最终触发完整性错误。

解决方法 #

方法1：修改字符串约束，用数值0代替false

直接把约束中的published = false改成published = 0，让SQLite能正确匹配数值类型的布尔值：

__table_args__ = (
    CheckConstraint(
        "(parent_id IS NULL AND title IS NOT NULL AND artwork_path IS NOT NULL) OR " 
        "(parent_id IS NOT NULL AND title IS NULL AND body IS NOT NULL) OR "
        "(published = 0 AND parent_id IS NULL AND artwork_path IS NOT NULL)",
        name="check_post_or_comment"
    ),
)

方法2：使用SQLAlchemy表达式API构造约束（更推荐）

用SQLAlchemy的逻辑表达式API代替字符串拼接，框架会自动根据数据库类型转换布尔值，避免手动写字符串的兼容性问题：

from sqlalchemy import and_, or_

class Entity(db.Model): 
    __tablename__ = 'entities'

    # 字段定义不变...

    __table_args__ = (
        CheckConstraint(
            or_(
                and_(
                    parent_id.is_(None),
                    title.isnot(None),
                    artwork_path.isnot(None)
                ),
                and_(
                    parent_id.isnot(None),
                    title.is_(None),
                    body.isnot(None)
                ),
                and_(
                    published == False,
                    parent_id.is_(None),
                    artwork_path.isnot(None)
                )
            ),
            name="check_post_or_comment"
        ),
    )

这两种方法都能让第三个约束条件正确生效，允许你插入published=false、parent_id=null且title=null的记录。

内容的提问来源于stack exchange，提问作者Radagast