Solved

Violation of PRIMARY KEY constraint.....Cannot insert duplicate key in object 'dbo.ItemAppliesTo'. error

Posted on 2010-09-20
26
1,551 Views
Last Modified: 2012-06-21

Hello,

I am trying to update all instances of a column (ResourceTargetGuid) in a Table (TBLE_A )
based on criteria in another table (Description field in TBLE_B = 'Marketing Group')
Here is my query:

UPDATE TBLE_A
SET ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
FROM TBLE_A ta INNER JOIN TBLE_B ta
ON tb.guid = ta.ItemGuid
WHERE tb.Description = 'Marketing Group'

Unfortunately, the following error is generated:

Violation of PRIMARY KEY constraint 'PK_TBLE_A'. Cannot insert duplicate key in object 'dbo.TBLE_A'.
The statement has been terminated.

Is there an edit in the query that can be made to work around this issue? TBLE_A has many records and based on this error I assume the column
ResourceTargetGuid is being used as a Primary key and I am attempting to update a record whcih already contains the value BDF0A71D-A058-480A-80FB-42F3571DBCED.
Please, any detail as to what I can do here is greatly appreciated.

Thank You
0
Comment
Question by:Charlie_Melega
  • 9
  • 8
  • 3
  • +3
26 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if you want to update "all" records affected, that single value cannot be a primary key value.so, there is some "design" issue there ...please clarify
0
 

Author Comment

by:Charlie_Melega
Comment Utility
yes, I want to update all records that are returned from the query:


select tb.name, ta.ResourceTargetGuid from TBLE_B tb, TBLE_A ta where ta.ItemGuid = tb.Guid AND tb.Description = 'Marketing Group'


I've attempted to translate this query into an Update statement. Is there an easier way where I can take the results of this query and update all of the values returned for ResourceTargetGuid with BDF0A71D-A058-480A-80FB-42F3571DBCED as I wanted to do in my Update statement above?

0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
check what columns included into primary key 'PK_TBLE_A'.  

You either need to change definition of this primary key or exclude duplicates from update.



0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
if your PK is defined as  (ResourceTargetGuid, col1, col2)
you need something like this
--------------
UPDATE TBLE_A
SET ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
FROM TBLE_A ta INNER JOIN TBLE_B ta
ON tb.guid = ta.ItemGuid
WHERE tb.Description = 'Marketing Group'
 and not exists (select 1 from TBLE_A where ResourceTargetGuid=  'BDF0A71D-A058-480A-80FB-42F3571DBCED' and col1=ta.col1 and col2=ta.col2)
0
 

Author Comment

by:Charlie_Melega
Comment Utility
trofimoval,

Thanks for you help so far.
I ran   exec sp_pkeys 'ItemAppliesTo' to obtain Primary Key definitions for TBLE_A and received the following:


CORP_Database      dbo      TBLE_A      ItemGuid                             1      PK_TBLE_A
CORP_Database      dbo      TBLE_A      ResourceTargetGuid       2      PK_TBLE_A


I proceeded to add the suggested      and not exists (select 1 from TBLE_A where ResourceTargetGuid=  'BDF0A71D-A058-480A-80FB-42F3571DBCED' and ItemGuid = ta.ItemGuid and ResourceTargetGuid = ta.ResourceTargetGuid)    to my UPDATE statement but continue to receive this annoying "Violation of PRIMARY KEY constraint....." error.
I'll be researching further but any additional suggestions are greatly appreciated.



0
 

Author Comment

by:Charlie_Melega
Comment Utility
just to add to this I did also try   and not exists (select 2 from TBLE_A where ResourceTargetGuid=  'BDF0A71D-A058-480A-80FB-42F3571DBCED' ...............................................................
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
When you run

SELECT * FROM TBLE_A
WHERE ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

Is it return anything?
0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
not exists only checks that you do not add duplicates to rows already in DB. This means your update statement produce those.

Query below will show you ItemGuid from table A that will produce duplicates. You need to define what to do with those. those are either duplicates that you want to delete before you do updates or you need additional condition in your update query to define which one needs to be updated and which one needs remain as is

select ItemGuid, min(ResourceTargetGuid ), max( ResourceTargetGuid ), count(*)
from TBLE_B tb join TBLE_A ta on ta.ItemGuid = tb.Guid
where tb.Description = 'Marketing Group'
group by ItemGuid having count(*)>1

0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
Actually, if something to select to check.
it is

SELECT ta.ResourceTargetGuid
FROM TBLE_A ta INNER JOIN TBLE_B tb
ON ta.ItemGuid = tb.Guid
WHERE tb.Description = 'Marketing Group'
AND ta.ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

If there is something return from this query.
sure you can't perform update unless you delete those records first.
0
 
LVL 2

Expert Comment

by:RCM01
Comment Utility
What are the columns that make up the Primary Key for your Table A?
0
 
LVL 2

Expert Comment

by:RCM01
Comment Utility
Sorry my post was after several of the updates,

Since there is a Record A that already represents the combination of B and this guid than the record already exists.

Are you trying to add another record or just ensure that it exists?

If you run your update this way you won't get the PK violoation but I don't know if it will accomplish what you are ultimately trying to  do

Update A
set ResourceTargetGuid = '....'
from B
inner join A
on b.guid = A.itemGuid
and isnull(A.ResourceTargetGuid,'') != '....'
left join A as t1
on A.itemGuid = t1.ItemGuid
and t1.ResourceTargetGuid = '....'
where b.Description = 'Marketing Group'
and t1.ItemGuid is null

This way you'll only get an A record that does't already exist in a relationship with the value you are setting
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
If there is a primary key violation, it just means that the operation you attemp would result in create a *logical* redundant information.  Your update statement need to be reviewed to make sure you are not dupplicating the same information (represented by the primary) another time/

Hope this helps...
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
'BDF0A71D-A058-480A-80FB-42F3571DBCED' already exists in table A, your update results in creating a dupplicate you should not have.  Don't drop the constraint.  Constraints are here for a reason.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Charlie_Melega
Comment Utility
Racimo,
 
Thanks for the detail. I attempted to implement  the query above and still receive the "Violation of PRIMARY KEY constraint" error.
Ater running queries per trofimoval, I know there are records in the ResourceTargetGuid column of TBLE_A that have the value of 'BDF0A71D-A058-480A-80FB-42F3571DBCED', I simply do not want to update those records. (deleting these records is not an option)
It appears that any query I've tried so far does not exclude these records in the UPDATE function thus resulting in the "Violation of PRIMARY KEY constraint".


0
 
LVL 2

Expert Comment

by:RCM01
Comment Utility
Run my query as a select and see if there multiple A records. If that's the case then you will need to determine which of the A records would need to be updated. It sounds like one b record can relate to many A records and by pj rule only one of these A record can be assigned the guid
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
Comment Utility
<<It appears that any query I've tried so far does not exclude these records in the UPDATE function thus resulting in the "Violation of PRIMARY KEY constraint".>>
Then you need to modify your query so that it does *not* update records that are already exist in the table.  You may use the NO EXISTS, NOT IN operator to do that...

Hope this helps...
0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
>>know there are records in the ResourceTargetGuid column of TBLE_A that have the value of 'BDF0A71D-A058-480A-80FB-42F3571DBCED', I simply do not want to update those records. (deleting these records is not an option)
Could you clarify?

Lets say query below show count of 3 rows for given ItemGuid. Do you want keep all 3 unchanged or update only 1 to new value?
If you can post sample of one of the duplicates and describe what you want, it will help to resolve your problem

select ItemGuid, min(ResourceTargetGuid ), max( ResourceTargetGuid ), count(*)
from TBLE_B tb join TBLE_A ta on ta.ItemGuid = tb.Guid
where tb.Description = 'Marketing Group'
group by ItemGuid having count(*)>1
0
 

Author Comment

by:Charlie_Melega
Comment Utility
Indeed one b record can relate to many many A records.   (where b record = b.guid and A record = A.ItemGuid)

I probably need to redefine the criteria and not reference A.ItemGuid.  As all I want to do is:

update all records in the ResourceTargetGuid column of TBLE_A  with value BDF0A71D-A058-480A-80FB-42F3571DBCED but only for those related records in TBLE_B where the 'Description' column = 'Marketing group'. I guess my poblem is I am having a hard time JOINING these 2 tables as TBLE_A contains 3 columns (ItemGuid, ResourceTargetGuid and Created Date) the first 2 columns are both Primary Keys.
0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
>>Indeed one b record can relate to many many A records.   (where b record = b.guid and A record = A.ItemGuid)
This is OK as long as they have different ItemGuid

Primary key on table A require combination of values in column ItemGuid, ResourceTargetGuid be unique.

>> update all records in the ResourceTargetGuid column of TBLE_A  with value BDF0A71D-A058-480A-80FB-42F3571DBCED but only for those related records in TBLE_B where the 'Description' column = 'Marketing group'

Problem is not just join. It is either data - you have duplicates in tableA or design - you primary key is missing some other column, or update
 My advise look on all columns in TBLE_A  that will become duplicate primary key after your update. Start just with one ItemGuid.  Is there any other columns that differentiate those rows? Then look on tableB "where the 'Description' column = 'Marketing group' May be you need some other criteria on this table to exclude duplicate
0
 

Author Comment

by:Charlie_Melega
Comment Utility
Thanks for the comments everyone, my battle continues
This issue continues to amaze me.

I went ahead and deleted all records from the ResourceTargetGuid column TBLE_A where the value was BDF0A71D-A058-480A-80FB-42F3571DBCED. Afterwards I verified that no records exist in this table\column with this value and yet I continue to receive the Primary Key constrain error when I try to UPDATE that column. I broke down the query to simply

Update TBLE_A
set ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

and still the Primary Key constrain error occurs.
I went ahead and deleted all instances of 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
in TBLE_B as well with no luck. (thinking the INNER JOIN  of TBBLE_B would require this)
I'm fine with deleting any records with this value and then running the UPDATE if it allows me to get around this Primary Constraint nightmare.
0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
Deleting rows with  "'BDF0A71D-A058-480A-80FB-42F3571DBCED' will not solve this problem!!!

You have duplicate ItemGuid in your table.
You PK is 2 columns, not one ItemGuid +ResourceTargetGuid

 you produce duplicate during updates, updating rows with the same ItemGuid
ItemGuid    ResourceTargetGuid
123            xxx
123            yyy
123            zzz

when you update those rows to 'DF0A71D-A058-480A-80FB-42F3571DBCED' you create duplicate PK row
=
0
 
LVL 11

Expert Comment

by:Larissa T
Comment Utility
this is sample data and code
---------------------------------
create table #TBLE_A  (ItemGuid int not null, ResourceTargetGuid varchar(10) not null, other int)
aLTER TABLE #TBLE_A ADD   PRIMARY KEY   (ItemGuid,ResourceTargetGuid)
-------------------------
insert into #TBLE_A
select 456, 'xxx',1
union select 456, 'yyy',1
union select 456, 'zzz',1
union select 123, 'xxx',1
union select 123, 'yyy',1
union select 123, 'zzz',1

create table #TBLE_B  (Guid int, Description varchar(20))
insert into #TBLE_B select 123, 'Marketing Group'
union select 456, 'not Marketing Group'
--------------------
update #TBLE_A set ResourceTargetGuid='new value'
-- this will produce pk violation error

-- this code will move dups into #TBLE_A_dups
insert into #TBLE_A_dups
select ta.*  into #TBLE_A_dups from #TBLE_B tb, #TBLE_A ta where ta.ItemGuid = tb.Guid AND tb.Description = 'Marketing Group'
delete ta from #TBLE_B tb, #TBLE_A ta where ta.ItemGuid = tb.Guid AND tb.Description = 'Marketing Group'

select * from #TBLE_A

-- this will move unique data only back to #TBLE_A

insert into #TBLE_A
select distinct ItemGuid, 'new value', other from #TBLE_A_dups
0
 

Author Comment

by:Charlie_Melega
Comment Utility
Yes, I do have duplicate records with 'BDF0A71D-A058-480A-80FB-42F3571DBCED'  in the ItemGuid column in TBLE_A
I may need re-design this to find other tables to accomplish this UPDATE
0
 

Author Comment

by:Charlie_Melega
Comment Utility
I didn't see you most recent post, thanks, will be testing this tomorrow
0
 

Author Comment

by:Charlie_Melega
Comment Utility
One last post on this as I am obviously not grasping the Primary Key issue adequately. This the actual query I am using that continues to give me the PK Constraint Errors

In summary, I am  attempting to UPDATE the column ResourceTargetGuiud in the ItemApplies to Table with GUID value 'BDF0A71D-A058-480A-80FB-42F3571DBCED'  on the condition the Enabled column from table ItemActive = False, Description column from the vItem view = 'This policy applies to Windows 2003' and to NOT update those records in the ResourceTargetGuid column of the ItemAppliesTo table where the value = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
I have a subquery in here as I don't see the proper method to JOIN 3 tables in an UPDATE.

Thanks again for any guidance on this.
I'm out.

Update ItemAppliesTo 

Set ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

from ItemActive

inner join  ItemAppliesTo 

on ItemActive.guid = ItemAppliesTo.itemGuid

where exists (select * from ItemActive iv, vItem i where iv.guid = i.guid

and iv.enabled = 'False' and i.description = 'This policy applies to Windows 2003')

and ItemAppliesTo.ResourceTargetGuid <> 'BDF0A71D-A058-480A-80FB-42F3571DBCED'

Open in new window

0
 
LVL 11

Accepted Solution

by:
Larissa T earned 500 total points
Comment Utility
your primary key is 2 columns, Think about it as 2 concatenated column that must be unique

Here is another sample

drop  table #ItemAppliesTo
create table #ItemAppliesTo  (ItemGuid int not null, ResourceTargetGuid varchar(36) not null, other int)
aLTER TABLE #ItemAppliesTo ADD   PRIMARY KEY   (ItemGuid,ResourceTargetGuid)
-------------------------
insert into #ItemAppliesTo
select 456, 'BDF0A71D-A058-480A-80FB-42F3571DBxxx',1
union select 456, 'BDF0A71D-A058-480A-80FB-42F3571DByyy',1
union select 456, 'BDF0A71D-A058-480A-80FB-42F3571DBzzz',1
union select 123, 'BDF0A71D-A058-480A-80FB-42F3571DBxxx',1
union select 123, 'BDF0A71D-A058-480A-80FB-42F3571DByyy',1
union select 123, 'BDF0A71D-A058-480A-80FB-42F3571DBzzz',1
union select 1, 'BDF0A71D-A058-480A-80FB-42F3571DBCED',1
union select 2, 'BDF0A71D-A058-480A-80FB-42F3571DBCED',2
union select 3, 'BDF0A71D-A058-480A-80FB-42F3571DBCED',3
union select 4, 'BDF0A71D-A058-480A-80FB-42F3571DBCED',4

drop table   #ItemActive
create table #ItemActive  (Guid int, Description varchar(80), enabled varchar(10))
insert into #ItemActive select 123, 'This policy applies to Windows 2003','False'
union select 456, 'another description', 'true'
--------------------
drop table   #vItem
create table #vItem  (Guid int, Description varchar(80), enabled varchar(10))
insert into #vItem select 123, 'This policy applies to Windows 2003','False'
union select 456, 'another description', 'true'

---------------------------------------------

--This is how your table will look after update do you see duplicate primary key row?
 select a.itemGuid, 'BDF0A71D-A058-480A-80FB-42F3571DBCED', a.other

--      Update a Set ResourceTargetGuid = 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
from #ItemActive b
inner join  #ItemAppliesTo a
on b.guid = a.itemGuid
where exists (select * from #ItemActive iv join  #vItem i on iv.guid = i.guid
      where iv.enabled = 'False' and i.description = 'This policy applies to Windows 2003')
      and a.ResourceTargetGuid <> 'BDF0A71D-A058-480A-80FB-42F3571DBCED'
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now