Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Why am I getting a primary/unique key violation?

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.


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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...