Sequelize删除表条目时遭遇外键约束问题求助

我在搭建简易API重新学习Sequelize ORM时，遇到了外键约束问题。ThingsToDoLists与ThingsToDoListTags通过ThingsToDoListTagJoins构成多对多关系，模型代码如下：

模型代码 #

thingstodolist.js #

'use strict';
module.exports = (sequelize, DataTypes) => {
  const ThingsToDoList = sequelize.define('ThingsToDoList', {
    listName: DataTypes.STRING(128),
    listDescription: DataTypes.STRING(128),
    userId: DataTypes.INTEGER,
  }, {});
  ThingsToDoList.associate = function(models) {
    
    ThingsToDoList.belongsTo(models.User, {
      foreignKey: "userId",
    })
    
    ThingsToDoList.belongsToMany(
      models.ThingsToDo,
      {
        through:"ThingsToDoTOThingsToDoListJoins",
        otherKey: "thingToDoId",
        foreignKey: "thingToDoListId"
      }
    )

    ThingsToDoList.belongsToMany(
      models.ThingsToDoListTag, 
      {
        through: "ThingsToDoListTagJoins",
        otherKey: "thingsToDoListTagId",
        foreignKey: "thingsToDoListId",
      }
    )
  };
  return ThingsToDoList;
};

thingstodotagjoins.js #

'use strict';
module.exports = (sequelize, DataTypes) => {
  const ThingsToDoListTagJoins = sequelize.define('ThingsToDoListTagJoins', {
    thingsToDoListId: DataTypes.INTEGER,
    thingsToDoListTagId: DataTypes.INTEGER
  }, {});
  ThingsToDoListTagJoins.associate = function(models) {
  };
  return ThingsToDoListTagJoins;
};

thingstodolisttag.js #

'use strict';
module.exports = (sequelize, DataTypes) => {
  const ThingsToDoListTag = sequelize.define('ThingsToDoListTag', {
    name: DataTypes.STRING(128)
  }, {});
  ThingsToDoListTag.associate = function(models) {
    ThingsToDoListTag.belongsToMany(models.ThingsToDoList,
      {
        through: "ThingsToDoListTagJoins",
        otherKey: "thingsToDoListId",
        foreignKey: "thingsToDoListTagId"
      })
  };
  return ThingsToDoListTag;
};

问题描述 #

我希望删除ThingsToDoList条目及其与ThingsToDoListTag的关联，但保留标签本身。执行await retrievedThingToDoList.destroy()时触发外键约束错误，尝试添加{"onDelete": "CASCADE", "hooks": true}配置后问题仍存在，错误信息如下：

Executing (default): DELETE FROM "ThingsToDoLists" WHERE "id" = 1
ForeignKeyConstraintError [SequelizeForeignKeyConstraintError]: update or delete on table "ThingsToDoLists" violates foreign key constraint "ThingsToDoListTagJoins_thingsToDoListId_fkey" on table "ThingsToDoListTagJoins"
    at Query.formatError (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/sequelize/lib/dialects/postgres/query.js:295:16)
    at /Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/sequelize/lib/dialects/postgres/query.js:72:18)
    at tryCatcher (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/async.js:102:5)
    at Async.drainQueues [as _onImmediate] (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/bluebird/js/release/async.js:15:14)
    at process.processImmediate (node:internal/timers:471:21) {
  parent: error: update or delete on table "ThingsToDoLists" violates foreign key constraint "ThingsToDoListTagJoins_thingsToDoListId_fkey" on table "ThingsToDoListTagJoins"
      at Parser.parseErrorMessage (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/aseefried/Projects/ThingsToDoApp/backend/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (node:events:513:28)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Readable.push (node:internal/streams/readable:234:10)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 374,
    severity: 'ERROR',
    code: '23503',
    detail: 'Key (id)=(1) is still referenced from table "ThingsToDoListTagJoins".',
    hint: undefined,
    position: undefined,
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: 'public',
    table: 'ThingsToDoListTagJoins',
    column: undefined,
    dataType: undefined,
    constraint: 'ThingsToDoListTagJoins_thingsToDoListId_fkey',
    file: 'ri_triggers.c',
    line: '2553',
    routine: 'ri_ReportViolation',
    sql: 'DELETE FROM "ThingsToDoLists" WHERE "id" = 1',
    parameters: undefined
  }

问题原因与解决方法 #

问题出在关联表的外键未配置级联删除规则，之前的onDelete配置位置不正确。Sequelize的级联删除是数据库层面的约束，需要在关联表的外键定义上生效，有两种可行方案：

方案1：在关联表模型中显式定义外键并添加级联规则 #

修改thingstodotagjoins.js，为thingsToDoListId外键添加onDelete: 'CASCADE'，这样删除ThingsToDoList时，会自动删除关联表中对应的记录：

'use strict';
module.exports = (sequelize, DataTypes) => {
  const ThingsToDoListTagJoins = sequelize.define('ThingsToDoListTagJoins', {
    thingsToDoListId: {
      type: DataTypes.INTEGER,
      references: {
        model: 'ThingsToDoLists',
        key: 'id'
      },
      onDelete: 'CASCADE'
    },
    thingsToDoListTagId: {
      type: DataTypes.INTEGER,
      references: {
        model: 'ThingsToDoListTags',
        key: 'id'
      }
    }
  }, {});
  ThingsToDoListTagJoins.associate = function(models) {
  };
  return ThingsToDoListTagJoins;
};

方案2：在belongsToMany关联中配置级联规则 #

如果不想修改关联表模型，可以在主模型的belongsToMany关联里，通过through对象的foreignKey选项设置级联：
修改thingstodolist.js中的多对多关联：

ThingsToDoList.belongsToMany(
  models.ThingsToDoListTag, 
  {
    through: {
      model: "ThingsToDoListTagJoins",
      foreignKey: "thingsToDoListId",
      onDelete: "CASCADE"
    },
    otherKey: "thingsToDoListTagId",
    foreignKey: "thingsToDoListId",
  }
)

关键注意事项 #

• 修改配置后，必须重新同步数据库（使用sequelize.sync({ force: true })或者迁移工具更新表结构），因为级联约束是数据库层面的规则，只有更新表结构才会生效。
• 如果之前已经创建了表，仅修改模型代码不会自动更新数据库约束，需要手动执行迁移或重新生成表。

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