Denali Enhancement–Duplicate Key Error Message

drsql — Thu, 14 Jul 2011 05:12:35 GMT

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!

Search results matching tags 'T-SQL', 'Constraints', and 'Denali'

Denali Enhancement–Duplicate Key Error Message