Sequelize删除用户触发外键约束冲突问题求助

我编写的deleteUser函数本应通过UUID删除用户，但测试执行失败，报错信息为：update or delete on table "Users" violates foreign key constraint "Photos_user_id_fkey" on table "Photos"，接口返回状态码为500而非预期的204。已在User模型中配置与Photo模型的hasMany关联，设置了foreignKey: 'user_id'和onDelete: 'CASCADE'，同时Photo迁移文件也配置了对应外键约束及onDelete: 'CASCADE'，但仍触发约束冲突，恳请排查原因及解决方法。

终端报错输出 #

✘ [fail]: deletetUser function should delete a user by uuid
    ℹ {
        error: 'update or delete on table "Users" violates foreign key constraint "Photos_user_id_fkey" on table "Photos"',
      }
  ─

  deletetUser function should delete a user by uuid
  tests/user/deleteUserTest.js:24

   23:     // Asserting that the status code of the response is 204
   24:     t.is(res.status, 204);                                  
   25:                                                             

  Difference (- actual, + expected):

  - 500
  + 204

  › tests/user/deleteUserTest.js:24:7

  ─

  1 test failed

相关代码 #

User模型 #

...

// Define a sequelize model for a User
module.exports = (sequelize, DataTypes) => {
    class User extends Model {
        /**
         * Helper method for defining associations.
         * This method is not a part of Sequelize lifecycle.
         * The `models/index` file will call this method automatically.
         */
        static associate(models) {
            // define association here
            User.hasMany(models.Photo, {
                    foreignKey: 'user_id',
                    as: 'photos',
                    onDelete: 'CASCADE'
                }),
            User.hasMany(models.Caption, {
                    foreignKey: 'user_id',
                    as: 'captions',
                    onDelete: 'CASCADE'
                }),
            User.hasMany(models.Vote, {
                    foreignKey: 'user_id',
                    as: 'votes',
                    onDelete: 'CASCADE'
                })
        }
...

User迁移文件 #

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
    async up(queryInterface, Sequelize) {
        await queryInterface.createTable('Users', {
            id: {
                allowNull: false,
                autoIncrement: true,
                type: Sequelize.INTEGER
            },
            uuid: {
                allowNull: false,
                unique: true,
                primaryKey: true,
                type: Sequelize.UUID,
                onDelete: 'CASCADE'
            },
...

Photo模型（注：原提供代码为迁移文件结构） #

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
    async up(queryInterface, Sequelize) {
        await queryInterface.createTable('Photos', {
            id: {
                allowNull: false,
                unique: true,
                autoIncrement: true,
                type: Sequelize.INTEGER
            },
            uuid: {
                allowNull: false,
                unique: true,
                primaryKey: true,
                type: Sequelize.UUID,
                onDelete: 'CASCADE'
            },
            url: {
                allowNull: false,
                unique: true,
                type: Sequelize.STRING
            },
            user_id: {
                allowNull: false,
                type: Sequelize.UUID,
                references: {
                    model: 'Users',
                    key: 'uuid'
                },
                onDelete: 'CASCADE' // cascade deletes to associated photos when a user is deleted
            },
...

Photo迁移文件 #

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
    async up(queryInterface, Sequelize) {
        await queryInterface.createTable('Photos', {
            id: {
                allowNull: false,
                unique: true,
                autoIncrement: true,
                type: Sequelize.INTEGER
            },
            uuid: {
                allowNull: false,
                unique: true,
                primaryKey: true,
                type: Sequelize.UUID,
                onDelete: 'CASCADE'
            },
            url: {
                allowNull: false,
                unique: true,
                type: Sequelize.STRING
            },
            user_id: {
                allowNull: false,
                type: Sequelize.UUID,
                references: {
                    model: 'Users',
                    key: 'uuid'
                },
                onDelete: 'CASCADE' // cascade deletes to associated photos when a user is deleted
            },
...

排查原因与解决方法 #

1. 确认迁移已正确生效 #

配置的onDelete: 'CASCADE'仅在迁移文件成功应用到数据库后才会生效。若先创建了Photos表，后续才添加CASCADE配置，旧的数据库约束不会自动更新：

• 解决：回滚并重新执行迁移（命令：sequelize db:migrate:undo:all → sequelize db:migrate），然后在数据库客户端（如pgAdmin、MySQL Workbench）查看Photos表的user_id外键约束详情，确认是否包含ON DELETE CASCADE规则。

2. 检查删除逻辑的正确性 #

确保deleteUser函数的删除语句正确：

• 确认使用User.destroy({ where: { uuid: 目标UUID } })，而非误删id字段；
• 若手动删除用户后未处理关联Photo，会直接触发约束错误，需依赖数据库级CASCADE自动删除关联数据，无需手动操作。

3. 补充反向关联配置 #

当前仅配置了User到Photo的hasMany关联，建议在Photo模型中补充反向关联（虽非CASCADE失效直接原因，但能保证关联逻辑完整性）：

// 在Photo模型的associate方法中添加
Photo.belongsTo(models.User, { foreignKey: 'user_id', as: 'user' });

4. 验证数据库引擎支持性 #

确保使用的数据库引擎支持外键CASCADE：

• MySQL需使用InnoDB引擎（MyISAM不支持外键）；
• PostgreSQL默认支持，无需额外配置。可在数据库中查看表的引擎类型确认。

5. 测试数据库直接删除行为 #

手动在数据库执行DELETE FROM Users WHERE uuid = '目标UUID';，观察关联Photo是否被自动删除：

• 若仍报错，说明数据库约束未配置CASCADE，问题出在迁移执行环节；
• 若自动删除成功，说明问题出在代码逻辑（如删除语句错误、事务未提交等）。

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