Duplicate Key Values by Anton Plotnikov

Closed

as Fixed

Type:	Suggestion
ID:	470542
Opened:	6/26/2009 12:20:37 AM
Access Restriction:	Public

Workaround(s)

view

When I try to insert duplicate keys in table with unique index I receiving an error message. And it isn't intelligible why key value was duplicated.

E.g.: Msg 2601, Level 14, State 1, Procedure UserSettingsStorageFiltered__TR__OnInsert, Line 11
Cannot insert duplicate key row in object 'FtmAppInterop.UserSettingsStorage' with unique index 'UserSettingsStorage__IXU__UserFilter'.

Details (expand)

English

SQL Engine

I suggest to add values and names of duplicated keys in error messages ##2601, 2627 and 3604.

E.g.: Msg 2601, Level 14, State 1, Procedure UserSettingsStorageFiltered__TR__OnInsert, Line 11
Cannot insert duplicate key row in object 'FtmAppInterop.UserSettingsStorage' with unique index 'UserSettingsStorage__IXU__UserFilter'. Duplicated values: [Path]='Key 1 value', [UserKey]=0x0000000001b5.

Improved User Interface

Improved Administration

0 attachments

Posted by Microsoft on 5/28/2010 at 8:34 AM

I have resolved it as a duplicate of connect id - 470542. We will look into it for addressing in the next release of SQL.

Thanks
Sunil

Posted by Anton Plotnikov on 7/22/2009 at 8:16 AM

Exactly

Posted by Microsoft on 7/21/2009 at 6:08 PM

Anton: thanks for contacting us for the issue. I agree that it will be useful to know which key violated the constraint. I played with following example

create table t(c1 int primary key, c2 int)
create table t_shadow(c1 int, c2 int)
insert into t values (1, 1)
insert into t_shadow values (1, 1)

-- this fails and it is not easy to know which row caused the failue.
-- Msg 2627, Level 14, State 1, Line 2
-- Violation of PRIMARY KEY constraint 'PK__t__3213663B03317E3D'. Cannot insert
-- duplicate key in object 'dbo.t'. The statement has been terminated.
insert into t select * from t_shadow

We will consider looking into it in a future release.

Thanks
Sunil

Microsoft