Flask-SQLAlchemy字符串字段添加外键及报错解决方案

问题场景 #

对接Semantic Scholar拉取的学术数据，构建作者与文献的多对多关联关系，两个关联字段均为字符串类型：

• authorId：Semantic Scholar作者ID，示例值144913779
• paperId：Semantic Scholar文献ID，示例值6d41ef098cbe439a33535ce1a2399d2cec7508c1

原有代码运行触发两类报错：

• VARCHAR requires a length on dialect MySQL
• author table already exists

报错根因 #

1. VARCHAR长度报错：MySQL方言要求所有VARCHAR类型字段显式指定长度，且外键关联的两个字段必须满足类型、长度、字符集/排序规则完全一致。原有代码中主表Publication的paperId长度为100，中间表对应外键字段长度设为99，长度不匹配；同时被外键引用的字符串字段未加唯一索引，MySQL不允许无索引的字段作为外键引用目标。另外原有代码中relationship配置错误，第一个参数误写为中间表名而非关联模型类名，也会触发字段识别异常。
2. 表已存在报错：之前运行代码已创建过结构不符合要求的author表，db.create_all()方法只会创建不存在的表，不会修改已有表结构，因此触发重复建表报错；另外原有中间表定义缺少闭合括号，存在语法错误。

正确实现代码 #

注意中间表需要定义在两个关联模型之前，避免名称引用错误：

# 多对多中间关联表
bibliography = db.Table(
    'bibliography',
    db.Model.metadata,
    # 外键字段长度与主表被引用字段完全一致，两个字段联合作为中间表主键
    db.Column('paperId', db.String(100), db.ForeignKey('publication.paperId'), primary_key=True),
    db.Column('authorId', db.String(20), db.ForeignKey('author.authorId'), primary_key=True)
)

class Author(db.Model):
    __tablename__ = "author"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 被外键引用的字符串字段必须加unique=True，自动创建唯一索引满足外键要求
    authorId = db.Column(db.String(20), nullable=False, unique=True)
    name = db.Column(db.String(30), nullable=False)
    affiliations = db.Column(db.Text(), nullable=True)

    # 多对多关系配置：第一个参数为另一侧关联模型类名，secondary指定中间表
    papers = db.relationship(
        'Publication',
        secondary=bibliography,
        backref=db.backref('authors', lazy='dynamic'),
        lazy='dynamic'
    )

class Publication(db.Model):
    __tablename__ = "publication"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # 被外键引用的字符串字段必须加unique=True，长度与中间表对应字段一致
    paperId = db.Column(db.String(100), nullable=False, unique=True)
    title = db.Column(db.Text(), nullable=False)

修复操作步骤 #

1. 处理已存在的旧表：
   • 开发环境：直接删除MySQL库中之前创建的所有业务表，重新执行db.create_all()即可生成符合约束的表结构
   • 生产环境：接入Flask-Migrate组件生成数据库迁移脚本，通过迁移完成表结构变更，禁止直接删表丢失数据
2. 校验字段规则：所有字符串类型外键、被引用字段的长度、字符集必须完全一致，被引用字段必须设置unique=True生成唯一索引，满足MySQL外键约束要求
3. 修正关系配置：db.relationship第一个参数必须传入另一侧的模型类名，中间表通过secondary参数指定，不要将中间表名作为关系关联目标。原有代码中Publication表内冗余的author字符串字段可以删除，多对多关联后直接通过publication.authors即可获取该文献对应的所有作者列表。

内容的提问来源于stack exchange，提问作者Vahidin Bajrić