使用SQLAlchemy闭包表时，不存在的父节点外键未触发异常？

尝试用SQLAlchemy的闭包表策略构建树形层级结构，先实现基础层级行为，再通过继承_vNode类扩展更多属性。代码能正常运行，但创建指向_vNode表中不存在的parent_id的_vLink时，未触发预期的异常，即使添加了ForeignKeyConstraint也无法解决问题。

核心实现代码：

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class _vBase(DeclarativeBase):
    pass

class _vNode(_vBase):
    __tablename__ = "vNode"

    id: Mapped[str] = mapped_column(primary_key=True)
    type: Mapped[str]
    
    __mapper_args__ = {
        "polymorphic_identity": "base_vNode",
        "polymorphic_on": "type",
    }

class _vLink(_vBase):
    __tablename__ = "base_link"

    parent_id: Mapped[str] = mapped_column(ForeignKey(_vNode.id), nullable=True,)
    parent = relationship(_vNode, foreign_keys=[parent_id])
    
    child_id: Mapped[str] = mapped_column(ForeignKey(_vNode.id), primary_key=True)
    child = relationship(_vNode, foreign_keys=[child_id])
    __table_args__ = (
        ForeignKeyConstraint(
            ['parent_id', 'child_id'],
            ['vNode.id', 'vNode.id']
        ),
    )    

测试代码：

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session
from sqlalchemy.schema import ForeignKeyConstraint
from sqlalchemy import create_engine
from sqlalchemy.orm import Session, sessionmaker

file = 'test_tree.db'
url = f'sqlite:///{file}'

engine = create_engine(url)
_vBase.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# 添加parent_id=None的节点正常
node1 = _vNode(id="node1")
link1 = _vLink(parent_id=None, child_id=node1.id)
session.add(node1)
session.add(link1)
session.commit()

# 添加parent_id为不存在值的节点未触发异常
node2 = _vNode(id="node2")
link2 = _vLink(parent_id="unexistent_id", child_id=node2.id)
session.add(node2)
session.add(link2)
session.commit()

核心原因：SQLite默认禁用外键约束 #

SQLite默认不启用外键约束检查，即便代码中定义了外键规则，数据库层面也不会执行校验，因此插入不存在的parent_id不会触发异常。

具体解决步骤 #

1. 开启SQLite外键约束
   创建引擎时，通过connect_args参数强制开启外键检查：

   engine = create_engine(url, connect_args={"check_same_thread": False, "foreign_keys": True})
   

   也可以在每次连接后执行SQL语句开启：

   with engine.connect() as conn:
       conn.execute("PRAGMA foreign_keys = ON")
       conn.commit()
   

2. 移除冗余的复合外键约束
   代码中通过ForeignKey已经分别为parent_id和child_id定义了独立外键，额外添加的ForeignKeyConstraint是冗余的——它定义的是复合外键（要求parent_id和child_id同时匹配vNode.id），不符合树形结构的逻辑，直接删除即可。

3. 验证约束生效
   修改后重新运行测试代码，插入不存在的parent_id时，SQLAlchemy会抛出IntegrityError，符合预期的异常触发行为。

修正后的核心代码 #

from sqlalchemy import ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class _vBase(DeclarativeBase):
    pass

class _vNode(_vBase):
    __tablename__ = "vNode"

    id: Mapped[str] = mapped_column(primary_key=True)
    type: Mapped[str]
    
    __mapper_args__ = {
        "polymorphic_identity": "base_vNode",
        "polymorphic_on": "type",
    }

class _vLink(_vBase):
    __tablename__ = "base_link"

    parent_id: Mapped[str] = mapped_column(ForeignKey(_vNode.id), nullable=True,)
    parent = relationship(_vNode, foreign_keys=[parent_id])
    
    child_id: Mapped[str] = mapped_column(ForeignKey(_vNode.id), primary_key=True)
    child = relationship(_vNode, foreign_keys=[child_id])

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