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.<\/p>\n
To demo, I created the following quickie table in tempdb.<\/p>\n
USE tempdb
GO<\/p>\n
–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<\/p>\n
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<\/p>\n
Then, inserting a duplicate row for the primary key value:<\/p>\n
INSERT INTO test.testErrorMessage (testErrorMessageId, otherColumn)
VALUES (1,’First’)
GO <\/p>\n
And on 2008 R2, I get:<\/p>\n
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PKtestErrorMessage’. Cannot insert duplicate key in object ‘test.testErrorMessage’.<\/p>\n
Now on Denali CTP3, you get a little bit more:<\/p>\n
Msg 2627, Level 14, State 1, Line 1 Then, to show the same thing for the UNIQUE CONSTRAINT:<\/p>\n INSERT INTO test.testErrorMessage (testErrorMessageId,otherColumn) On 2008 R2, you get the following<\/p>\n Msg 2627, Level 14, State 1, Line 4 And again on Denali CTP3:<\/p>\n Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint ‘PKtestErrorMessage’. Cannot insert duplicate key in object ‘test.testErrorMessage’. The duplicate key value is (1).<\/strong><\/p>\n
VALUES (2,’First’)<\/p>\n
Violation of UNIQUE KEY constraint ‘AKtestErrorMessage’. Cannot insert duplicate key in object ‘test.testErrorMessage’.<\/p>\n
Violation of UNIQUE KEY constraint ‘AKtestErrorMessage’. Cannot insert duplicate key in object ‘test.testErrorMessage’. The duplicate key value is (First).<\/strong><\/p>\n