THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Denali Enhancement–Duplicate Key Error Message

When I was editing my chapter on implementing a database, I noticed a really nice improvement in the error message I had from the previous edition of the book. Instead of just telling me that there was a value in my modification statement that duplicated an existing value (or multiple values affected by the statement), it told me the duplicated value.

To demo, I created the following quickie table in tempdb.

USE tempdb
GO

--drop the object if it initially existed
if object_id('test.testErrorMessage') IS NOT null
    DROP TABLE test.testErrorMessage
IF schema_id('test') IS NOT NULL
    DROP SCHEMA test
go

CREATE SCHEMA test
GO
CREATE TABLE test.testErrorMessage
(
    testErrorMessageId INT NOT NULL
        CONSTRAINT PKtestErrorMessage PRIMARY KEY,
    otherColumn varchar(10) NOT NULL
        CONSTRAINT AKtestErrorMessage UNIQUE (otherColumn),
)
GO
INSERT INTO test.testErrorMessage (testErrorMessageId, otherColumn)
VALUES (1,'First')
GO

Then, inserting a duplicate row for the primary key value:

INSERT INTO test.testErrorMessage (testErrorMessageId, otherColumn)
VALUES (1,'First')
GO

And on 2008 R2, I get:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'.

Now on Denali CTP3, you get a little bit more:

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

Then, to show the same thing for the UNIQUE CONSTRAINT:

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (2,'First')

On 2008 R2, you get the following

Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'.

And again on Denali CTP3:

Msg 2627, Level 14, State 1, Line 4
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (First).

You can see if you duplicate > 1 value, it gives you one of the items. It might be better if the message didn’t imply that it was the only duplicate value, but hey, it is a great improvement. If you think it ought to be tweaked to say “A duplicated key value is (…) or something, click here)

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (5,'Third'),(6,'Third'),(3,'Second'),(4,'Second')

Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'AKtestErrorMessage'. Cannot insert duplicate key in object 'test.testErrorMessage'. The duplicate key value is (Third).

It works with indexes also:

ALTER TABLE test.testErrorMessage
     DROP CONSTRAINT AKtestErrorMessage

CREATE UNIQUE INDEX UXtestErrorMessage ON test.testErrorMessage(otherColumn)

INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn)
VALUES (5,'Third'),(6,'Third'),(3,'Second'),(4,'Second')

You get the following:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'test.testErrorMessage' with unique index 'UXtestErrorMessage'. The duplicate key value is (Third).

Much nicer!

Published Thursday, July 14, 2011 1:12 AM by drsql
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Uri Dimant said:

Hi Louis

That is definitely great improvement. Just wonder what is about the below error?

"Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated."

July 14, 2011 1:25 AM
 

Uri Dimant said:

Hi Louis

That is definitely great improvement. Just wonder what is about the below error?

"Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated."

July 14, 2011 1:25 AM
 

jamiet said:

Oh nice. I like that a lot!

July 14, 2011 1:45 AM
 

TiborKaraszi said:

Isn't it nice, how we can be happy about these small things! :)

July 14, 2011 7:21 AM
 

drsql said:

Uri, yeah, that really is annoying isn't it. This connect item about that is one of my best vote getters ever:

https://connect.microsoft.com/SQLServer/feedback/details/125347/improved-error-message-when-you-try-to-put-too-large-a-value-in-a-column

July 26, 2011 4:54 PM
 

Raj said:

I got same error message (msg 2627) when i inserting values into table. Table contains one primary key column having Idenetity (True and ON) incremented by 1. So I'm not giving any value while inserting new rows..... Ideally it should manage by SQL Server itself.

Your help will highly appreciated!

March 21, 2012 9:33 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement