续集和Node.js：使用级联"set null"删除时的外键约束

Question

我正在使用sequelize版本4.4.10，node.js和sqlite。这是我的模型：

subscription.js

module.exports = (sequelize, DataTypes) => {
  let Subscription = sequelize.define('Subscription', {
    id: { type: DataTypes.STRING(50), primaryKey: true },
    title: DataTypes.STRING(100),
    url: DataTypes.STRING(50),
    thumbnail_url: DataTypes.STRING(50)
  })
  Subscription.associate = (models) => {
    Subscription.belongsToMany(models.Tag, {
      through: models.TagSubscription,
      onDelete: 'set null',
      onUpdate: 'set null'
    })
  }
  return Subscription
}

tag.js

module.exports = (sequelize, DataTypes) => {
  let Tag = sequelize.define('Tag', {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    title: { type: DataTypes.STRING(25) }
  })
  Tag.associate = models => {
    Tag.belongsToMany(models.Subscription, {
      through: models.TagSubscription,
      onDelete: 'set null',
      onUpdate: 'set null'
    })
  }
  return Tag
}

tagsubscription.js

module.exports = (sequelize, DataTypes) => {
  let TagSubscription = sequelize.define('TagSubscription', {
  })
  return TagSubscription
}

这是我的订阅存储库：

sisscriptionRepository.js

let models = require('../models')
let logger = require('bug-killer')
class SubscriptionRepository {
  async deleteAll () {
    try {
      await models.Subscription.destroy({ where: {} })
    } catch (error) {
      logger.error('SubscriptionRepository.deleteAll() error')
      throw (error)
    }
  }
  async findOne (subId) {
    try {
      let subscription = await models.Subscription.findOne({ where: { id: subId } })
      return subscription
    } catch (error) {
      logger.error('SubscriptionRepository.findOne() error')
      throw (error)
    }
  }
  async create (sub) {
    try {
      let subscription = await models.Subscription.create({
        id: sub.id,
        title: sub.title,
        url: sub.url,
        thumbnail_url: sub.thumbnail_url
      })
      return subscription
    } catch (error) {
      logger.error('SubscriptionRepository.create() error')
      throw (error)
    }
  }
}
exports.SubscriptionRepository = SubscriptionRepository

这是我要测试的功能：

async refreshData () {
    try {
      let extracted = this.getExtractedData(undefined)
      await subscriptionRepository.deleteAll()
      await models.Subscription.bulkCreate(extracted.items, { ignoreDuplicates: true, updateOnDuplicates: ['title', 'url', 'thumbnail_url', 'createdAt', 'updatedAt'] })
    } catch (error) {
      console.log(error)
    }
  }

当我致电RefreshData时，它会记录以下错误：

Executing (default): DELETE FROM `Subscriptions`
error SubscriptionRepository.deleteAll() error
{ SequelizeForeignKeyConstraintError: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
    at Query.formatError (/home/xavier/Projets/youtube_manager/node_modules/sequelize/lib/dialects/sqlite/query.js:374:18)
    at Statement.afterExecute (/home/xavier/Projets/youtube_manager/node_modules/sequelize/lib/dialects/sqlite/query.js:119:32)
    at Statement.replacement (/home/xavier/Projets/youtube_manager/node_modules/sqlite3/lib/trace.js:19:31)
  name: 'SequelizeForeignKeyConstraintError',
  parent:
   { Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
     errno: 19,
     code: 'SQLITE_CONSTRAINT',
     sql: 'DELETE FROM `Subscriptions`' },
  original:
   { Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
     errno: 19,
     code: 'SQLITE_CONSTRAINT',
     sql: 'DELETE FROM `Subscriptions`' },
  sql: 'DELETE FROM `Subscriptions`',
  fields: undefined,
  table: undefined,
  value: undefined,
  index: undefined }

为什么这个错误？由于我为删除和更新cascade指定了"设置null"行为，因此destry（）函数不应在违规中失败。

Answer 1

尝试

onDelete: 'cascade',
onUpdate: 'cascade'

，因为如果没有，它不能将其设置为null