Sequelize and Node.js : deleting cascade "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
}

这是我的订阅存储库：

SubscriptionRepository.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 }

为什么会出现这个错误？由于我为delete和update cascade都指定了"set null“行为，销毁()函数应该不会因为违反外键而失败。

Answer 1

试一试

onDelete: 'cascade',
onUpdate: 'cascade'

因为如果不存在，它就不能将其设置为null