Why am I getting a primary/unique key violation?

3

July 23, 2014 by Kenneth Fisher

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

PK_Error


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.

3 thoughts on “Why am I getting a primary/unique key violation?

  1. Chuck says:

    I can think of another cause that I’ve recently encountered – a varchar key column that contains trailing spaces. We import data from an Oracle server. Oracle allows such spaces and treats the keys as unique if you have one with the spaces, and one without. SQL trims the trailing spaces and therefore treats them as identical.

    • Technically I would say that comes under the third option. You are inserting duplicates (to SQL) at the same time. In your particular case if you want to avoid the problem you could probably insert the rows into CHAR columns instead of VARCHAR. CHAR columns do keep the trailing spaces and will consider them unique.

      • Chuck says:

        We opted to convert them to underscores which will work just fine… until we discover a key on the source server that is the same except with undescores. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,135 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: