Sequelize模型含deletedAt的联合唯一索引失效求助

核心原因 #

这不是Sequelize的问题，是数据库对NULL值的处理规则导致的：
大多数关系型数据库（比如MySQL、PostgreSQL）中，NULL被定义为「未知值」，所以在唯一索引判断里，多个NULL不会被视为相等的内容。你的联合索引是(userId, name, deletedAt)，当两条记录的deletedAt都是NULL时，数据库会认为这两个NULL不相等，因此不会触发唯一约束，允许重复创建。

而当你只保留userId和name的唯一索引时，这两个字段都是非NULL（userId是外键不允许null，name也设置了allowNull: false），所以能正常触发约束。

解决办法 #

根据你用软删除的场景（希望同一个用户下，未删除的Script名称唯一），推荐以下几种方案：

方案1：给deletedAt设置非NULL默认值

把未删除记录的deletedAt设为一个固定的特殊值（比如远过去的时间），而不是NULL，这样所有未删除记录的deletedAt值一致，就能让联合索引生效：

const Script = sequelize.define('Script', {
  name: {
    type: DataTypes.STRING,
    allowNull: false,
  },
  deletedAt: {
    type: DataTypes.DATE,
    allowNull: false, // 改为不允许null
    defaultValue: new Date('1970-01-01') // 默认值设为固定时间
  },
}, {
  indexes: [
    {
      unique: true,
      fields: ['userId', 'name', 'deletedAt']
    }
  ],
  timestamps: true,
  paranoid: true,
  // 覆盖paranoid默认的null逻辑，软删除时设置为当前时间
  hooks: {
    beforeDestroy: (instance) => {
      instance.deletedAt = new Date();
    }
  }
});

方案2：使用部分唯一索引（推荐）

如果你的数据库支持部分索引（比如PostgreSQL、MySQL 8.0+），可以创建一个仅对deletedAt为NULL的记录生效的唯一索引，这样既保留软删除的NULL逻辑，又能实现未删除记录的唯一约束：

const Script = sequelize.define('Script', {
  // ... 字段定义不变
}, {
  indexes: [
    {
      unique: true,
      fields: ['userId', 'name'],
      // 针对PostgreSQL的部分索引写法
      where: {
        deletedAt: null
      },
      // 如果是MySQL 8.0+，用expression替代where
      // expression: 'deletedAt IS NULL'
    }
  ],
  timestamps: true,
  paranoid: true
});

这个方案更贴合软删除的设计，因为只有未删除的记录会被约束，已删除的记录（deletedAt非NULL）不受影响。

方案3：应用层前置校验

在创建Script之前，先查询是否存在相同userId、name且deletedAt为NULL的记录，如果存在则抛出错误：

// 创建Script的业务逻辑里
const existingScript = await Script.findOne({
  where: {
    userId: targetUserId,
    name: targetName,
    deletedAt: null
  }
});
if (existingScript) {
  throw new Error('该用户下已存在同名未删除的脚本');
}
// 继续创建逻辑

这个方案依赖应用层逻辑，适合所有数据库，但要注意并发场景下可能出现竞态（比如两个请求同时查询都没找到，然后同时创建），需要配合数据库事务或锁来避免。

内容的提问来源于stack exchange，提问作者C. Yee