如何在SQLAlchemy中创建非空自引用外键

这种非空自引用外键的模式完全可以实现，不需要将parent_id改为可空，下面是几种可落地的实现方案：

方案1：利用flush机制分两步写入（兼容多数数据库） #

这是最通用的方案，思路是先插入记录拿到自增id，再在同一个事务内更新parent_id为自身id。PostgreSQL等支持外键延迟检查的数据库可以直接使用，MySQL需要临时调整外键检查策略。

from sqlalchemy import Column, Integer, ForeignKey, text
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyModel(Base):
    __tablename__ = "mytable"
    id = Column(Integer, primary_key=True)
    # PostgreSQL需要配置外键为延迟检查，事务提交时才校验约束
    parent_id = Column(Integer, ForeignKey("mytable.id", deferrable=True, initially="DEFERRED"), nullable=False)

# 根节点创建代码
session = Session()
# 临时填入任意整数值作为占位，后续会覆盖
root = MyModel(parent_id=0)
session.add(root)
# flush触发数据库生成自增ID，此时root.id已被赋值但事务未提交
session.flush()
# 修正parent_id为自身ID
root.parent_id = root.id
# 提交事务，外键约束校验通过
session.commit()

MySQL适配注意：InnoDB引擎默认不支持延迟外键检查，使用该方案时需要在当前会话临时关闭外键检查：

session.execute(text("SET FOREIGN_KEY_CHECKS = 0"))
# 执行根节点插入、更新操作
session.commit()
session.execute(text("SET FOREIGN_KEY_CHECKS = 1"))

方案2：使用数据库原生函数单条语句插入 #

如果使用PostgreSQL/MySQL这类支持获取当前会话自增ID的数据库，可以直接用数据库内置函数作为parent_id的插入值，单条语句即可完成插入，不需要二次更新：

# PostgreSQL示例：用currval获取当前表序列的最新生成值
root = MyModel(parent_id=text("currval('mytable_id_seq'::regclass)"))
session.add(root)
session.commit()

# MySQL示例：用2505246获取当前会话最新生成的自增ID
root = MyModel(parent_id=text("2505246"))
session.add(root)
session.commit()

方案3：预分配根节点ID #

如果业务场景中根节点是固定唯一的，可以直接指定根节点ID为固定值，不需要依赖自增生成：

root = MyModel(id=1, parent_id=1)
session.add(root)
session.commit()

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