SQL Server error 2601: 'Cannot insert duplicate key row in object with unique index'

Cannot insert duplicate key row in object 'dbo.Members' with unique index 'UX_Members_Email'. The duplicate key value is (maria.garcia@example.test). (error 2601) means a unique index already holds that value. The message names the index and the value, so look the existing row up, then either update it instead or insert only when the value is free.

Error 2601 and error 2627 are the same refusal. 2601 comes from a CREATE UNIQUE INDEX, 2627 from a UNIQUE or PRIMARY KEY constraint, so code that catches duplicates should test for both numbers.

The error

INSERT INTO dbo.Members (FullName, Email) VALUES (N'Maria G.', N'maria.garcia@example.test');

Msg 2601, Level 14, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Cannot insert duplicate key row in object 'dbo.Members' with unique index 'UX_Members_Email'. The duplicate key value is (maria.garcia@example.test).
The statement has been terminated.

Why it happens

The table had CREATE UNIQUE INDEX UX_Members_Email ON dbo.Members (Email) and already contained Maria Garcia's address. A second row with the same email would break the index, so SQL Server rolled the statement back. Typical sources are a form submitted twice, a sign-up that does not check for an existing account, or an import that repeats rows.

To see the difference from 2627, the index was dropped and the same rule declared as a constraint, ALTER TABLE dbo.Members ADD CONSTRAINT UQ_Members_Email UNIQUE (Email). The same insert then failed with a different number and wording:

Msg 2627, Level 14, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 1
Violation of UNIQUE KEY constraint 'UQ_Members_Email'. Cannot insert duplicate key in object 'dbo.Members'. The duplicate key value is (maria.garcia@example.test).

Both are severity 14 and both stop only the statement. Which one you see depends purely on how the uniqueness was declared, so a database whose indexes were created by a tool may give 2601 where you expected 2627.

The fix

DECLARE @Email nvarchar(200) = N'maria.garcia@example.test';
INSERT INTO dbo.Members (FullName, Email)
SELECT N'Maria G.', @Email
WHERE NOT EXISTS (SELECT 1 FROM dbo.Members WITH (UPDLOCK, HOLDLOCK) WHERE Email = @Email);
SELECT @@ROWCOUNT AS RowsInserted;

For the existing address this inserted 0 rows and raised no error; the same statement for david.chen@example.test inserted 1. The UPDLOCK, HOLDLOCK hints make the statement take its locks before the check, so the check and the insert belong together; only single-session runs were made here. The unique index still matters: it is the guarantee, and the check only avoids the error in the normal case. When the row should be changed rather than skipped, update it by the key instead. The final table also showed Id 4 for David Chen, because the failed inserts had used identity values.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e2601.sql on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. A members table with an identity key and a unique index on the email column, then the same table with a unique constraint instead. Names and addresses are fictional.

Frequently asked

What is the difference between SQL Server error 2601 and 2627?

Both reject a duplicate value. 2601 comes from a unique index created with CREATE UNIQUE INDEX; 2627 comes from a UNIQUE or PRIMARY KEY constraint. Catch both numbers in application code.

How do I find which row causes 'Cannot insert duplicate key row'?

The message names the index and the duplicate value. Select from the table where the indexed column equals that value to see the row that is already there.

How do I insert only if the value does not exist in SQL Server?

Use INSERT ... SELECT ... WHERE NOT EXISTS with UPDLOCK and HOLDLOCK on the check, so the test and the insert happen together. Keep the unique index as the real guarantee.

The constraint version has its own post, error 2627, violation of PRIMARY KEY constraint, with an IF NOT EXISTS and a MERGE variant. More decoded errors in the Fixes category.