如何在TypeORM中为包含可空字段的组合创建唯一约束

问题说明 #

你当前使用TypeORM的@Unique装饰器定义username和deletedAt的复合唯一约束，在PostgreSQL 12中无法达到软删场景下的用户名唯一预期：PostgreSQL标准唯一约束中，NULL值被视为互不相等，因此允许存在多条username相同、deletedAt为NULL的未删除用户记录。

问题解答 #

1. 是否推荐单独编写原生查询实现？
   是，TypeORM内置的@Unique装饰器不支持自定义约束逻辑，无法满足可空列的特殊唯一规则，使用原生SQL创建唯一索引是最直接可靠的方案。

2. 能否不借助迁移工具完成？
   可以。你可以直接通过psql、DBeaver等任意PostgreSQL客户端连接到数据库，手动执行唯一索引创建语句即可，无需依赖TypeORM或其他迁移工具。如果是多环境部署、团队协作的场景，建议将SQL语句写入迁移脚本保证环境一致性。

3. 可空列复合唯一约束实现方案
   你可以使用带COALESCE函数的唯一索引实现需求，将deletedAt为NULL的记录统一替换为一个固定的时间戳值，保证未删除用户的username全局唯一：

CREATE UNIQUE INDEX idx_username_unique ON users 
(username, COALESCE(deletedAt, '1970-01-01 00:00:00'::timestamptz));

如果希望在TypeORM实体中定义该索引避免手动执行SQL，可以将原来的@Unique装饰器替换为@Index装饰器：

// 替换原来的@Unique('username-unique',['username', 'deletedAt'])
@Index('idx_username_unique', ['username', () => 'COALESCE(deletedAt, \'1970-01-01 00:00:00\'::timestamptz)'], { unique: true })

代码修正提示 #

你贴出的实体代码存在语法错误，@DeleteDateColumn配置中type字段后缺少逗号，可空字段也建议显式声明null类型：

@DeleteDateColumn({
  type: 'timestamp with time zone', // 补充缺失的逗号
  nullable: true,
})
deletedAt: Date | null // 补充null类型声明

内容的提问来源于stack exchange，提问作者jd.lee