We help IT Professionals succeed at work.

Cannot update table (Violation of PRIMARY KEY constraint)

Medium Priority
798 Views
Last Modified: 2013-11-30
Hello i have two tables tblPollutants and Pollutant
When i am trying to run the query that exists in the code Snippet: i am getting this error:
Violation of PRIMARY KEY constraint 'PK_tblPollutants'. Cannot insert duplicate key in object 'dbo.tblPollutants'.
the query  finds similar values between the two tables and updates the id from the Pollutant table


i have attached an sql script in order to create the two tables


This is the query where i am facing the problem
 
Update    tblPollutants
set tblPollutants.PollutantID = Pollutant.PollutantId
FROM         tblPollutants INNER JOIN
                      Pollutant ON tblPollutants.Pollutant = Pollutant.PollutantName

Open in new window

Insert.txt
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
If those are autonumbered primary key fields, they won't necessarily match between tables and you can't change one to match the other when the other table has a record with that id already existing hence your error message.  The main point though is you shouldn't be.  If these ids were to match in the first place, the design of the tables/database should be reviewed for possible change and use trigger, cascading keys, or handle inserts in order where you use the SCOPE_IDENTITY() of first insert for subsequent ones.
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
You are trying to update the primary key column with a value thats already there that's why it is throwing the error

try this on the backup database
Update    tblPollutants
set tblPollutants.PollutantID = Pollutant.PollutantId
FROM         tblPollutants INNER JOIN
Pollutant ON tblPollutants.Pollutant = Pollutant.PollutantName
WHERE tblPollutants.PollutantID <> Pollutant.PollutantId
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Your Pollutant ID 287, 310 are repeated twice and hence you obtain the error.

Fix it out in the INSERT statements to get it corrected.

Author

Commented:
aneeshattingal: ti gives me the same error  your query
Violation of PRIMARY KEY constraint 'PK_tblPollutants'. Cannot insert duplicate key in object 'dbo.tblPollutants'.
The statement has been terminated.


rrjegan17:  
when i am running this query i  see this values only once
select  pollutantid from pollutant
where pollutantid in (287,310)


mwvisa1: i am joining them on nvarchar column tblPollutants.Pollutant = Pollutant.PollutantName
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
If you see in the sample Inserts provided "Carbon tetrachloride" repeats twice in both the tables.
Kindly check that record and fix it.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
My point is that those tables primary keys don't have to match.  If they are joined by Pollutant, you can just ensure that the value of that field and PollutantName are set as unique in each of tables.  To have the numbers match, you would have to review design as stated as you probably don't want to insert these separately as currently doing as each table can assign different auto id based on transactions that occurred before.

Author

Commented:
rrjegan17:  Thanks a lot how did you managed to find that? it seems to working right now let me check it on my main database and i will accept your answer

mwvisa1: Those tables primary keys have to match in my case because  i just want to copy the values from table a to table b for for a data warehouseproject

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you all

Author

Commented:
mwvisa1: yes i could use something like this but i wanted to use the cascade update option if i had delete the values from the tables and insert new values the cascade update  option will not work
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.