📋

Description

This error indicates an attempt to insert a duplicate value into a column with a unique index or constraint. It typically occurs during INSERT or UPDATE operations when the new value violates the uniqueness rule.

💬

Error Message

Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.

🔍

Known Causes

4 known causes

⚠️

Duplicate INSERT Value

An INSERT statement is trying to insert a row with a key value that already exists in the unique index.

⚠️

Conflicting UPDATE Value

An UPDATE statement is modifying a row's key column to a value that already exists in the unique index.

⚠️

Data Synchronization Issues

During data synchronization or replication, duplicate key values might be introduced due to timing or configuration problems.

⚠️

Incorrect Identity Handling

If using IDENTITY columns, incorrect seeding or manual insertion of values can lead to duplicate key errors.

🛠️

Solutions

4 solutions available

1. Find Duplicate Key Value easy

Identify the duplicate causing unique index violation

1

Find which index is violated

-- Error shows index name
-- Find index columns:
SELECT 
    i.name AS index_name,
    c.name AS column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id 
    AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id 
    AND ic.column_id = c.column_id
WHERE i.name = 'IX_Employees_Email';

2

Check for existing duplicate

-- See if value already exists:
SELECT * FROM Employees WHERE email = 'john@example.com';

-- Find all duplicates in column:
SELECT email, COUNT(*) as cnt
FROM Employees
GROUP BY email
HAVING COUNT(*) > 1;

2. Use IGNORE_DUP_KEY Index Option medium

Silently ignore duplicate inserts

1

Create index with IGNORE_DUP_KEY

-- Create unique index that ignores duplicates:
CREATE UNIQUE INDEX IX_Email 
ON Employees(email)
WITH (IGNORE_DUP_KEY = ON);

-- Duplicate inserts are silently skipped instead of error
-- Warning: data is lost without notification!

3. Handle in INSERT Logic medium

Check before insert or use MERGE

1

Check before inserting

IF NOT EXISTS (SELECT 1 FROM Employees WHERE email = @email)
    INSERT INTO Employees (name, email) VALUES (@name, @email);

2

Use TRY/CATCH

BEGIN TRY
    INSERT INTO Employees (name, email) VALUES ('John', 'john@example.com');
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2601
        PRINT 'Email already exists, skipping...';
    ELSE
        THROW;
END CATCH

4. Clean Up Duplicates medium

Remove existing duplicates before adding constraint

1

Delete duplicates keeping first

WITH CTE AS (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY email ORDER BY id) AS rn
    FROM Employees
)
DELETE FROM CTE WHERE rn > 1;

-- Now create unique index:
CREATE UNIQUE INDEX IX_Email ON Employees(email);

SQL Server Duplicate Key