📋

Description

Error 2627 indicates a violation of a unique constraint or primary key constraint in a SQL Server database. This error occurs when attempting to insert or update data that would result in a duplicate key value within a table.

💬

Error Message

Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.

🔍

Known Causes

4 known causes

⚠️

Duplicate Primary Key

An attempt was made to insert a record with a primary key value that already exists in the table. Primary keys must be unique.

⚠️

Duplicate Unique Index

An attempt was made to insert or update a record, resulting in a duplicate value in a column with a unique index. This index enforces uniqueness for the column's values.

⚠️

Incorrect Identity Specification

If using an identity column, the seed or increment may be configured incorrectly, leading to duplicate values being generated.

⚠️

Concurrency Issues

In concurrent environments, multiple processes might try to insert the same key value simultaneously before the uniqueness constraint can be enforced.

🛠️

Solutions

4 solutions available

1. Find Duplicate Value easy

Identify which value violates the constraint

1

Check constraint details

-- Error message shows constraint name
-- Find constraint columns:
SELECT 
    kc.name AS constraint_name,
    c.name AS column_name
FROM sys.key_constraints kc
JOIN sys.index_columns ic ON kc.unique_index_id = ic.index_id 
    AND kc.parent_object_id = ic.object_id
JOIN sys.columns c ON ic.column_id = c.column_id 
    AND ic.object_id = c.object_id
WHERE kc.name = 'PK_Employees';

2

Find duplicate in table

-- Check if value already exists:
SELECT * FROM Employees WHERE employee_id = 123;

-- Find all duplicates:
SELECT employee_id, COUNT(*)
FROM Employees
GROUP BY employee_id
HAVING COUNT(*) > 1;

2. Use IDENTITY for Auto-Increment easy

Let SQL Server generate unique IDs

1

Use IDENTITY column

-- Create table with identity:
CREATE TABLE Employees (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100)
);

-- Insert without specifying ID:
INSERT INTO Employees (name) VALUES ('John');
-- ID is auto-generated

2

Get the generated ID

-- After INSERT, get the new ID:
INSERT INTO Employees (name) VALUES ('John');
SELECT SCOPE_IDENTITY() AS NewID;

3. Use MERGE for Upsert medium

Insert or update based on existence

1

MERGE statement

MERGE INTO Employees AS target
USING (SELECT 123 AS id, 'John' AS name) AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET name = source.name
WHEN NOT MATCHED THEN
    INSERT (id, name) VALUES (source.id, source.name);

2

Simple IF EXISTS approach

IF EXISTS (SELECT 1 FROM Employees WHERE id = 123)
    UPDATE Employees SET name = 'John' WHERE id = 123;
ELSE
    INSERT INTO Employees (id, name) VALUES (123, 'John');

4. Reset IDENTITY Seed medium

If IDENTITY value conflicts with existing data

1

Check and reseed IDENTITY

-- Check current identity value:
DBCC CHECKIDENT ('Employees', NORESEED);

-- Find max ID in table:
SELECT MAX(id) FROM Employees;

-- Reseed to continue after max:
DBCC CHECKIDENT ('Employees', RESEED, 1000);

SQL Server Duplicate Key Violation