开启paranoid:true时Sequelize关联表级联destroy()失效问题

开启paranoid: true软删除后，删除父记录时关联的子记录未被同步删除，该问题已存在超8年。尝试复现Sequelize官方测试用例但失败。

测试代码 #

'use strict';

const { Sequelize, DataTypes } = require('sequelize');

(async () => {
    const sequelize = new Sequelize('x', 'x', 'x', {
        host: 'localhost',
        dialect: 'mysql',
        define: {
            charset: 'utf8mb4',
            freezeTableName: false,
            createdAt: 'created_at',
            updatedAt: 'updated_at',
            deletedAt: 'deleted_at',
            timestamps: true,
            underscored: true,
            paranoid: true,
        },
        //logging: (...msg) => console.log(msg),
    });

    const Task = sequelize.define('Task', { title: DataTypes.STRING });
    const User = sequelize.define('User', { username: DataTypes.STRING });

    User.hasMany(Task, { foreignKey: { onDelete: 'cascade' } });

    await sequelize.sync({ force: true });

    const [user, task] = await Promise.all([
        User.create({ username: 'foo' }),
        Task.create({ title: 'task' }),
    ]);

    await user.setTasks([task]);
    await user.destroy();
    const tasks = await Task.findAll();

    console.log('tasks:', tasks);
})();

疑问点 #

1. 代码中设置onDelete: 'cascade'，但Sequelize生成的Tasks表创建SQL为FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE，原因是什么？
2. 脚本执行时仅更新Tasks表的user_id字段，未更新deleted_at字段，原因是什么？

执行日志 #

x % node cascade
Executing (default): DROP TABLE IF EXISTS `tasks`;
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'tasks' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='x' AND REFERENCED_TABLE_NAME IS NOT NULL;
Executing (default): SELECT CONSTRAINT_NAME as constraint_name,CONSTRAINT_NAME as constraintName,CONSTRAINT_SCHEMA as constraintSchema,CONSTRAINT_SCHEMA as constraintCatalog,TABLE_NAME as tableName,TABLE_SCHEMA as tableSchema,TABLE_SCHEMA as tableCatalog,COLUMN_NAME as columnName,REFERENCED_TABLE_SCHEMA as referencedTableSchema,REFERENCED_TABLE_SCHEMA as referencedTableCatalog,REFERENCED_TABLE_NAME as referencedTableName,REFERENCED_COLUMN_NAME as referencedColumnName FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = 'users' AND CONSTRAINT_NAME!='PRIMARY' AND CONSTRAINT_SCHEMA='x' AND REFERENCED_TABLE_NAME IS NOT NULL;
Executing (default): DROP TABLE IF EXISTS `tasks`;
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): DROP TABLE IF EXISTS `users`;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER NOT NULL auto_increment , `username` VARCHAR(255), `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `deleted_at` DATETIME, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Executing (default): SHOW INDEX FROM `users`
Executing (default): DROP TABLE IF EXISTS `tasks`;
Executing (default): CREATE TABLE IF NOT EXISTS `tasks` (`id` INTEGER NOT NULL auto_increment , `title` VARCHAR(255), `created_at` DATETIME NOT NULL, `updated_at` DATETIME NOT NULL, `deleted_at` DATETIME, `user_id` INTEGER, PRIMARY KEY (`id`), FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Executing (default): SHOW INDEX FROM `tasks`
Executing (default): INSERT INTO `users` (`id`,`username`,`created_at`,`updated_at`) VALUES (DEFAULT,?,?,?);
Executing (default): INSERT INTO `tasks` (`id`,`title`,`created_at`,`updated_at`) VALUES (DEFAULT,?,?,?);
Executing (default): SELECT `id`, `title`, `created_at`, `updated_at`, `deleted_at`, `user_id` AS `UserId` FROM `tasks` AS `Task` WHERE (`Task`.`deleted_at` IS NULL AND `Task`.`user_id` = 1);
Executing (default): UPDATE `tasks` SET `user_id`=?,`updated_at`=? WHERE (`deleted_at` IS NULL AND `id` IN (1))
Executing (default): UPDATE `users` SET `deleted_at`=?,`updated_at`=? WHERE `id` = ?
Executing (default): SELECT `id`, `title`, `created_at`, `updated_at`, `deleted_at`, `user_id` AS `UserId` FROM `tasks` AS `Task` WHERE (`Task`.`deleted_at` IS NULL);
tasks: [
  Task {
    dataValues: {
      id: 1,
      title: 'task',
      created_at: 2022-11-21T20:38:12.000Z,
      updated_at: 2022-11-21T20:38:12.000Z,
      deleted_at: null,
      UserId: 1
    },
    _previousDataValues: {
      id: 1,
      title: 'task',
      created_at: 2022-11-21T20:38:12.000Z,
      updated_at: 2022-11-21T20:38:12.000Z,
      deleted_at: null,
      UserId: 1
    },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      raw: true,
      attributes: [Array]
    },
    isNewRecord: false
  }
]

环境信息 #

• Sequelize版本：sequelize@6.25.4, @sequelize/core@7.0.0-alpha.10
• Node.js版本：v16.15.1
• 数据库及版本：MySQL 8.x（原输入版本号为笔误修正）
• 连接器库及版本：mysql2@2.3.3

问题解答 #

1. onDelete: 'cascade'被改为ON DELETE SET NULL的原因 #

当父模型开启paranoid软删除时，Sequelize会自动修改外键的onDelete行为：软删除仅标记父记录的deleted_at字段，不会真正删除数据库中的父记录，因此数据库层面的CASCADE级联删除无法触发。为了避免外键关联的约束冲突，Sequelize默认会将onDelete改为SET NULL。

如果需要强制保留onDelete: 'cascade'，需在关联配置中显式关闭关联的paranoid属性，覆盖全局设置：

User.hasMany(Task, { 
  foreignKey: { onDelete: 'cascade' },
  paranoid: false
});

2. 脚本仅更新user_id而非deleted_at的原因 #

• 软删除父模型时，Sequelize仅会更新父记录的deleted_at字段，不会自动级联软删除关联的子记录。日志中user_id被更新为NULL是数据库层面ON DELETE SET NULL约束触发的行为。
• 若要实现级联软删除，需手动添加逻辑：可以在父模型的beforeDestroy钩子中主动软删除关联子记录，示例代码如下：

User.beforeDestroy(async (user) => {
  await Task.update(
    { deleted_at: new Date() },
    { where: { user_id: user.id, deleted_at: null } }
  );
});

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