CREATE UNIQUE INDEX statement terminated because a duplicate key was found

Question

I want to check the duplicate values in my SQL table. and remove those data.

So I first checked the duplicate values using

SELECT COUNT(*)
      [Se_Id],
      [Service_Previous_Step],
      [Service_Next_Step]
FROM   [Mondo-PROD].[dbo].[ServiceWorkflows]
GROUP BY
        [Se_Id],
        [Service_Previous_Step],
        [Service_Next_Step]
HAVING COUNT(*) > 1;

Then I use this

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

But I get errors as

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.ServiceWorkflows' and the index name 'UQ_ServiceWorkflows_ServiceId_PreviousStep_NextStep'. The duplicate key value is (96, 1, 2).

Adding a constraint does not delete duplicated data. If data should be deleted, you need to execute a delete command. — Jonas Metzler, Dec 10 '22 at 04:48

score 0 · Accepted Answer · answered Dec 10 '22 at 04:55

You can use DELETE to remove duplicates from your table like:

WITH CTE AS(
   SELECT [Se_Id],
      [Service_Previous_Step],
      [Service_Next_Step],
       RN = ROW_NUMBER()OVER(PARTITION BY Se_Id ORDER BY Se_Id)
   FROM [dbo].[ServiceWorkflows] 
)
DELETE FROM CTE WHERE RN > 1

If your table already has duplicate records in it then adding a constraint won't delete the duplicate records and hence the error which you are getting.

CREATE UNIQUE INDEX statement terminated because a duplicate key was found

1 Answers1