|
|
Posted by Roy Harvey on 06/20/07 19:27
>UserInfo_Login nonclustered, unique located on PRIMARY tp_SiteID,
>tp_Login, tp_Deleted
OK, the UNIQUE contraint is on three columns, the combination of
tp_SiteID, tp_Login and tp_Deleted.
The UPDATE command is not doing an INSERT, but it is changing the
value of the column so that the combination of those three columns
conflicts with data already in the table - it woud violate the UNIQUE
contraint.
If you run the query:
SELECT *
FROM Deve2_SITE..UserInfo
WHERE tp_Login IN ( N'DEV\sam', N'DEV2\sam')
ORDER BY tp_Login, tp_SiteID, tp_Deleted
That will show the rows that are in conflict. What you are looking
for is that there is already DEV2\sam for at least one matching pair
of tp_SiteID/tp_Deleted values.
Hope that helps.
Roy Harvey
Beacon Falls, CT
On Wed, 20 Jun 2007 11:55:41 -0700, SJ <sjourdan@gmail.com> wrote:
>On Jun 20, 1:42 pm, Alex Kuznetsov <AK_TIREDOFS...@hotmail.COM> wrote:
>> On Jun 20, 11:51 am, SJ <sjour...@gmail.com> wrote:
>>
>> > Hi!
>>
>> > I am trying to do a simple udpate on a table and I am getting the
>> > error:
>>
>> > "Cannot insert duplicate key row in object 'UserInfo' with unique
>> > index 'UserInfo_Login'.The statement has been terminated."
>>
>> > Here is the command I am trying to run
>> > ---
>> > USE Deve2_SITE
>>
>> > update UserInfo set tp_Login=N'DEV2\sam', tp_Email=N't...@test.com',
>> > tp_Title=N'Samson, Sammy'
>> > WHERE (tp_Login=N'DEV\sam')
>> > --
>>
>> > Any ideas?
>>
>> In addition to Roy's advice, do you have triggers on your table?
>>
>> http://sqlserver-tips.blogspot.com/- Hide quoted text -
>>
>> - Show quoted text -
>
>Thanks all. I guess i dont know what an index is. And why an update
>would matter. This is the result of the EXEC command:
>
>UserInfo_FullText nonclustered, unique located on PRIMARY tp_GUID
>UserInfo_Login nonclustered, unique located on PRIMARY tp_SiteID,
>tp_Login, tp_Deleted
>UserInfo_PK clustered, unique, primary key located on PRIMARY
>tp_SiteID, tp_ID
>UserInfo_SID nonclustered, unique located on PRIMARY tp_SiteID,
>tp_SystemID
>
>ANy ideas? Why is an update command doing an insert (that is probably
>a dumb question)
[Back to original message]
|