Flask SQLAlchemy、Alembic与SQLite：阻止删除被外键引用对象

问题背景 #

现有transactions和categories两张表，transactions通过category_id外键关联categories的id。原本预期删除被交易引用的分类时数据库会抛出错误，但实际操作中可以直接删除，导致交易里留存无效的category_id；更糟的是，删除分类后新创建的分类会复用旧ID，直接引发交易关联混乱。

当前技术栈：用Flask-SQLAlchemy定义模型，Alembic做数据库迁移，底层数据库是SQLite。

现有模型代码 #

class TransactionModel(db.Model):
    __tablename__ = 'transactions'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    date = db.Column(db.Date, nullable=False)
    amount = db.Column(db.Numeric(precision=10, scale=2), nullable=False)
    concept = db.Column(db.String(100), nullable=False)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id', ondelete='RESTRICT'), nullable=True)
    category = db.relationship("CategoryModel")

class CategoryModel(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(24), nullable=False)
    description = db.Column(db.String(128), nullable=False)

异常表现 #

• 创建ID为1、2的分类，以及关联这两个分类的交易后，删除ID=2的分类，交易仍保留category_id=2；此时新建分类会复用ID=2，导致旧交易错误关联到新分类。
• 删除ID=1的分类后，新建分类却用ID=3，复用逻辑无规律。
• 已尝试在迁移中执行PRAGMA foreign_keys = ON，但外键约束依旧不生效。

需求目标 #

1. 禁止删除被任何交易引用的分类，仅允许删除无关联的分类；
2. 禁止复用已删除的分类ID，删除后该ID永久不可用（类似Oracle的序列机制）。

解决方案 #

一、修复外键约束生效问题 #

SQLite的外键约束默认是关闭的，而且必须在每个数据库连接建立时开启，仅执行一次或在迁移中设置无法永久生效。

1. 在Flask应用全局开启外键约束

在Flask应用初始化时，添加连接事件监听，确保每个新连接都自动开启外键约束：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///your_db.db'
db = SQLAlchemy(app)

# 监听连接建立事件，强制开启外键约束
@db.event.listens_for(db.engine, 'connect')
def enable_foreign_keys(conn, record):
    cursor = conn.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

2. 验证效果

重启应用后，尝试删除被交易引用的分类，此时数据库会抛出FOREIGN KEY constraint failed错误，直接阻止删除操作，达到预期效果。

二、禁止分类ID复用 #

SQLite普通的自增主键会复用已删除的ID，而添加AUTOINCREMENT属性后，ID会严格递增，永不复用已删除的ID（类似Oracle序列）。

1. 修改CategoryModel的主键定义

给CategoryModel的id字段添加sqlite_autoincrement=True参数：

class CategoryModel(db.Model):
    __tablename__ = 'categories'
    id = db.Column(db.Integer, primary_key=True, sqlite_autoincrement=True, nullable=False)
    name = db.Column(db.String(24), nullable=False)
    description = db.Column(db.String(128), nullable=False)

2. 生成并执行迁移脚本

运行Alembic命令生成修改表结构的迁移脚本，再应用迁移：

alembic revision --autogenerate -m "enable sqlite_autoincrement for categories.id"
alembic upgrade head

原理说明

添加sqlite_autoincrement=True后，SQLite会自动创建sqlite_sequence表来记录每个表的最大ID值，即使删除了旧记录，新插入的ID也会从当前最大值+1开始，彻底避免ID复用。

最终验证 #

1. 尝试删除被交易引用的分类：数据库抛出外键约束错误，无法删除；
2. 删除无关联的分类后，新建分类的ID会从当前最大ID+1开始，不会复用已删除的ID。

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