Fluent Migrator添加唯一约束时报could not create unique index错误如何解决

错误原因 #

PostgreSQL错误码23505属于唯一约束冲突类错误，出现这个问题的核心原因是当前dbo.customer表中已经存在重复的id_number字段值，数据库不允许在存在重复数据的场景下创建全局唯一约束。

解决步骤 #

第一步：先查询表中已有的重复数据

执行以下SQL即可查出所有重复的id_number、重复次数以及对应关联的行主键，方便定位处理数据：

SELECT id_number, COUNT(*) AS repeat_count, ARRAY_AGG(id) AS related_record_ids
FROM dbo.customer
GROUP BY id_number
HAVING COUNT(*) > 1;

第二步：根据业务场景选择处理方案

方案1：全局要求id_number唯一（大多数业务场景的选择）

1. 按照第一步查到的重复数据，根据业务规则清理：可以修改重复的id_number为唯一值，或者删除无效的重复行，确保全表id_number无重复。
2. 补全Fluent Migrator的回滚逻辑，避免后续迁移回滚出错，修改后的迁移代码如下：

public override void Down()
{
    Execute.Sql("ALTER TABLE dbo.customer DROP CONSTRAINT IF EXISTS id_number_unique;");
}

public override void Up()
{
    Execute.Sql("ALTER TABLE dbo.customer ADD CONSTRAINT id_number_unique UNIQUE (id_number);");
}

3. 重新执行迁移即可成功。
   后续业务代码写入数据时，建议提前校验id_number唯一性，避免出现写入报错。

方案2：允许历史数据重复，仅要求新增数据唯一

如果业务场景不需要追溯处理历史重复数据，只要求后续新写入的id_number保持唯一，可以创建带过滤条件的部分唯一索引替代全局唯一约束。
举个例子，如果你表中有软删除标记is_deleted，只需要保证未删除的数据id_number唯一，可以用以下SQL：

CREATE UNIQUE INDEX id_number_unique ON dbo.customer (id_number) WHERE is_deleted = false;

如果没有软删除字段，也可以用创建时间作为过滤条件，比如仅对2024年之后新增的数据加唯一约束：

CREATE UNIQUE INDEX id_number_unique ON dbo.customer (id_number) WHERE create_time >= '2024-01-01';

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