If you’re getting an error that reads something like “Violation of PRIMARY KEY constraint ‘PK_CatId’. Cannot insert duplicate key in object ‘dbo.Cats’. The duplicate key value is (1)” in SQL Server, it’s because you’re trying to insert a duplicate value into a primary key column.

A primary key cannot contain duplicate values.

To fix this issue, you’ll need to change the value you’re trying to insert into the primary key column.

Example of Error

Here’s an example of code that produces the error:

INSERT INTO Cats (CatId, CatName) VALUES (1, 'Purr');

Output:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_CatId'. Cannot insert duplicate key in object 'dbo.Cats'. The duplicate key value is (1).

This error occurred because I tried to insert a duplicate value into the CatId column, which happens to be the primary key column for this table.

To verify this, let’s select the data from the table:

SELECT * FROM Cats;

Output:

CatId  CatName
-----  -------
1      Scratch

As expected, there’s already a cat with a CatId of 1 (actually, it’s the only cat in this case).

Solution

To fix this issue, change the value that you’re trying to insert into the primary key column. Specifically, change it to a unique value.

Example:

INSERT INTO Cats (CatId, CatName) VALUES (2, 'Purr');

Output:

(1 row affected)

This time it worked. It worked because I incremented the value I was trying to insert to 2, and the column didn’t yet contain that value

We can check the table:

SELECT * FROM Cats;

Output:

CatId  CatName
-----  -------
1      Scratch
2      Purr   

Yes, the new cat was inserted.

If you find it difficult to keep track of the existing IDs and this error occurs a lot, consider making the primary key an identity column. That is, use the IDENTITY property for your primary key columns.