Sequelize迁移非主键字段添加外键触发SQLite报错问题

这个SQLITE_ERROR: foreign key mismatch和Sequelize本身逻辑无关，是SQLite的硬性规则限制：SQLite支持外键关联非主键字段，但要求被引用的字段必须创建唯一索引。你当前AssetClasses表的name字段既不是主键，也没有加唯一约束，SQLite无法保证该字段值不重复，外键关联的唯一性前提不成立，所以直接抛出错误。
迁移过程中残留的Assets_backup表，是Sequelize适配SQLite的机制导致的：SQLite不支持直接通过ALTER TABLE语句添加外键约束，Sequelize会自动走表重建流程——创建临时备份表、迁移存量数据、删除原表、将备份表重命名为正式表，流程中途报错就会把临时备份表残留在库中，手动删除即可。

按以下步骤调整即可正常创建约束：

• 给被引用的AssetClasses.name字段添加唯一约束
  你可以直接修改创建AssetClasses表的迁移文件，给name字段增加unique: true配置，也可以单独写一个迁移给该字段添加唯一索引，字段配置示例：

  // 修改create-asset-class迁移中的name字段定义
  name: {
    type: Sequelize.STRING,
    allowNull: false,
    unique: true // 新增该行，为字段创建唯一索引
  },
  

• 确认两边关联字段的属性完全匹配
  你当前Assets表的assetClass字段是Sequelize.STRING类型，和AssetClasses.name的类型一致，这部分不需要调整。注意如果两边字段的类型、排序规则(collation)不匹配，也会触发相同的外键报错。
• 重新执行迁移
  先手动删除残留的Assets_backup表，将数据库回滚到添加外键约束的迁移之前的版本，再重新执行迁移即可正常创建外键。

外键创建成功后，还要同步修改Sequelize的模型关联定义，不要用默认的主键关联逻辑，手动指定关联字段，否则后续查询会出错：

Asset.belongsTo(AssetClass, {
  foreignKey: 'assetClass',
  targetKey: 'name' // 指定关联AssetClass的name字段，而非默认的id主键
})
AssetClass.hasMany(Asset, {
  foreignKey: 'assetClass',
  sourceKey: 'name'
})

如果是已经有存量数据的生产环境，加唯一约束之前先检查AssetClasses.name列有没有重复值，存在重复值的话先清理重复数据，否则唯一索引会创建失败。

实际项目中不推荐长期使用非主键字段做外键关联：字符串类型做外键比整数主键更占存储空间，关联查询性能更差，后续如果要修改AssetClass的name值，还需要级联更新所有关联的Assets表记录，维护成本很高。你之前用assetClassId关联主键的方案是更符合数据库设计规范的实践。

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