SQL创建唯一约束遇重复键错误：如何排查并删除表中重复数据？

首先修正你的重复项排查语句（原语句中Se_Id和约束里的Service_Id不一致，会导致漏查重复项）：

SELECT COUNT(*) AS 重复次数,
       [Service_Id],
       [Service_Previous_Step],
       [Service_Next_Step]
FROM   [Mondo-PROD].[dbo].[ServiceWorkflows]
GROUP BY
       [Service_Id],
       [Service_Previous_Step],
       [Service_Next_Step]
HAVING COUNT(*) > 1;

方法一：使用CTE+ROW_NUMBER()精准删除重复项 #

这种方法会给每组重复数据标记行号，仅保留每组中的一行（可指定保留最早/最新行），删除其余重复项：

WITH DuplicateRows AS (
    SELECT 
        [Service_Id],
        [Service_Previous_Step],
        [Service_Next_Step],
        -- 按重复列分组分配行号，若有主键（如Id），替换ORDER BY子句可指定保留目标行
        ROW_NUMBER() OVER (
            PARTITION BY [Service_Id], [Service_Previous_Step], [Service_Next_Step]
            ORDER BY (SELECT NULL) -- 示例：换成ORDER BY Id ASC保留最早行，ORDER BY Id DESC保留最新行
        ) AS RowNum
    FROM [Mondo-PROD].[dbo].[ServiceWorkflows]
)
DELETE FROM DuplicateRows
WHERE RowNum > 1;

方法二：循环删除重复项（适合小数据量） #

通过循环逐步删除每组重复项中的多余行：

WHILE EXISTS (
    SELECT 1
    FROM [Mondo-PROD].[dbo].[ServiceWorkflows]
    GROUP BY [Service_Id], [Service_Previous_Step], [Service_Next_Step]
    HAVING COUNT(*) > 1
)
BEGIN
    DELETE TOP (1) FROM [Mondo-PROD].[dbo].[ServiceWorkflows]
    WHERE EXISTS (
        SELECT 1
        FROM [Mondo-PROD].[dbo].[ServiceWorkflows] AS t2
        WHERE t2.[Service_Id] = [ServiceWorkflows].[Service_Id]
          AND t2.[Service_Previous_Step] = [ServiceWorkflows].[Service_Previous_Step]
          AND t2.[Service_Next_Step] = [ServiceWorkflows].[Service_Next_Step]
        GROUP BY t2.[Service_Id], t2.[Service_Previous_Step], t2.[Service_Next_Step]
        HAVING COUNT(*) > 1
    )
END

关键操作步骤 #

1. 备份数据：执行删除前务必备份表，防止误操作：

SELECT * INTO [Mondo-PROD].[dbo].[ServiceWorkflows_Backup] FROM [Mondo-PROD].[dbo].[ServiceWorkflows];

2. 验证删除结果：删除后重新运行修正后的排查语句，确认无重复项：

SELECT COUNT(*) AS 重复次数,
       [Service_Id],
       [Service_Previous_Step],
       [Service_Next_Step]
FROM   [Mondo-PROD].[dbo].[ServiceWorkflows]
GROUP BY
       [Service_Id],
       [Service_Previous_Step],
       [Service_Next_Step]
HAVING COUNT(*) > 1;

3. 添加唯一约束：确认无重复后，执行原约束语句即可成功：

ALTER TABLE [dbo].[ServiceWorkflows] 
ADD CONSTRAINT UQ_ServiceWorkflows_ServiceId_PreviousStep_NextStep 
UNIQUE ([Service_Id], [Service_Previous_Step], [Service_Next_Step]);

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