This may seem like a question with a simple answer but there is a bit more to it than you might think. In fact I know of 3 possible reasons (and there may be more I don’t know) for seeing a primary key error. Technically they occur for any unique key, of which the primary key is one of possibly many, and they all boil down to trying to end up with two rows in the table that “match” based on the unique key.

For my examples I’m going to use the AdventureWorks2012.Person.Address table and I’ll actually be hitting a unique key not the primary key. This is because I’m lazy and it was the first one I found with a unique key I could work with easily. You can take my word for it that a primary key will react exactly the same way.

Here are the top 3 rows of Person.Address with just the columns we care about.

SELECT TOP 3 AddressLine1, AddressLine2, 
City, StateProvinceID, PostalCode
FROM Person.Address
ORDER BY AddressID

Inserting a row that already exists in the table.

This is by far the most common cause of a unique/primary key error that I see. A row exists in the table and you try to insert another one with the same key data.

INSERT INTO Person.Address (AddressLine1, AddressLine2, 
City, StateProvinceID, PostalCode)
VALUES ('1970 Napa Ct.',NULL, 'Bothell',79,'98011')

With a result of

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object ‘Person.Address’ with unique index ‘IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode’. The duplicate key value is (1970 Napa Ct., , Bothell, 79, 98011).

The statement has been terminated.

Note that the name of the unique index that is violated is listed along with the duplicate key data. If you are inserting multiple rows causing a unique/primary key violation then only on key value set is listed. In my testing it was always the first duplicate found but I couldn’t guarantee it.

Updating a row that causes a duplicate.

This one is also fairly common. In this you are updating a row that causes a duplicate to occur.

UPDATE Person.Address 
SET AddressLine1 = '1970 Napa Ct.' 
WHERE AddressID = 2

With the same result.

Msg 2601, Level 14, State 1, Line 1

Cannot insert duplicate key row in object ‘Person.Address’ with unique index ‘IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode’. The duplicate key value is (1970 Napa Ct., , Bothell, 79, 98011).

The statement has been terminated.

Inserting two (or more) identical rows

This one really throws people. It’s by far the least common cause of the error and the first one I think of when I hear “I checked but there are no duplicates.” or “The table is empty how can I be getting a primary key error?”

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'DupTest')
DROP TABLE DupTest;
GO
SELECT TOP 0 AddressLine1, AddressLine2, 
City, StateProvinceID, PostalCode INTO DupTest 
FROM Person.Address;
CREATE UNIQUE INDEX ixu_DupTest ON DupTest(AddressLine1, 
AddressLine2, City, StateProvinceID, PostalCode);
INSERT INTO DupTest
SELECT AddressLine1, AddressLine2, City, 
StateProvinceID, PostalCode 
FROM Person.Address
UNION ALL
SELECT AddressLine1, AddressLine2, City, 
StateProvinceID, PostalCode 
FROM Person.Address;
GO

I am creating a brand new table so we can be certain I’m not inserting a row that already exists in the table. I’m not updating anything. So why am I getting an error? If you look at the insert statement you will see that I’m inserting every row from Person.Address twice. If SQL actually allowed this to run I would end up with duplicates in the table. The simple method I use to check for this particular problem is to wrap the problem query in an “outer” query to check for the duplicate. Like so:

SELECT -- List of columns from the unique/primary key
AddressLine1, AddressLine2, City, 
StateProvinceID, PostalCode 
FROM (
-- Query we need to find the duplicates from
SELECT AddressLine1, AddressLine2, City, 
StateProvinceID, PostalCode 
FROM Person.Address
UNION ALL
SELECT AddressLine1, AddressLine2, City, 
StateProvinceID, PostalCode 
FROM Person.Address
) x
GROUP BY -- List of columns from the unique/primary key
AddressLine1, AddressLine2, City, 
StateProvinceID, PostalCode 
HAVING COUNT(1) > 1

Any rows that turn up are duplicates in the query and will return you a unique/primary key error.

The important point to remember here is that a unique/primary key error is not always caused by inserting a row with unique column data that already exists. Once you have checked the destination table it’s time to check your source data as well.

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, problem resolution, sql statements, T-SQL