// SQL Server error reference

SQL Server error 2627: Violation of PRIMARY KEY or UNIQUE KEY

An INSERT or UPDATE would duplicate a value in a PRIMARY KEY or UNIQUE constraint. The message names the constraint, table and duplicate key value.

Error number
2627
Severity
14
Category
Constraints and keys
Error message
Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.

What it means

Primary key and unique constraints guarantee that no two rows share the same key. When a write would create a duplicate, SQL Server stops the statement with 2627, followed by 3621 ("The statement has been terminated"). The message says which kind of constraint (PRIMARY KEY or UNIQUE KEY), its name, the table, and since SQL Server 2017 the duplicate value itself.

Error 2601 is the same failure on a unique index that is not a constraint.

Common causes

  • Inserting a row whose key already exists, for example re-running a load.
  • Inserting explicit values into an identity column with IDENTITY_INSERT on, or an identity seed reset below existing values.
  • A batch insert from a source that itself contains duplicate keys.
  • Two sessions inserting the same key concurrently: the check-then-insert pattern (IF NOT EXISTS ... INSERT) isn't safe without locking hints.

How to fix it

  1. Find the existing row using the value shown in the message.

    SELECT * FROM dbo.Customers WHERE Email = N'[email protected]';
  2. Skip rows that already exist when loading.

    INSERT INTO dbo.Customers (Email, Name)
    SELECT s.Email, s.Name
    FROM staging.Customers AS s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Customers AS c WHERE c.Email = s.Email);
  3. Look for duplicates inside the source before inserting.

    SELECT Email, COUNT(*) FROM staging.Customers GROUP BY Email HAVING COUNT(*) > 1;
  4. For an upsert that is safe under concurrency, update first and insert if nothing was updated, holding a range lock.

    BEGIN TRANSACTION;
    UPDATE dbo.Customers WITH (UPDLOCK, SERIALIZABLE) SET Name = @Name WHERE Email = @Email;
    IF @@ROWCOUNT = 0
        INSERT INTO dbo.Customers (Email, Name) VALUES (@Email, @Name);
    COMMIT;
  5. If an identity column collides, check and reseed it.

    DBCC CHECKIDENT ('dbo.Customers', NORESEED);  -- shows current and max values
    DBCC CHECKIDENT ('dbo.Customers', RESEED);

Reproduce it safely

CREATE TABLE #k (id int PRIMARY KEY);
INSERT INTO #k VALUES (1);
INSERT INTO #k VALUES (1);  -- Msg 2627 ... The duplicate key value is (1).
DROP TABLE #k;

Questions

What is the difference between error 2627 and 2601?

2627 is raised by a PRIMARY KEY or UNIQUE constraint; 2601 by a unique index created with CREATE UNIQUE INDEX. The cause and the fixes are the same.

Can I make SQL Server ignore duplicate keys?

A unique index created WITH (IGNORE_DUP_KEY = ON) discards duplicate rows in an INSERT with a warning instead of failing the statement. Use it deliberately, since it silently drops data.

Related errors

More constraints and keys errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.