Solved

Cannot update table (Violation of PRIMARY KEY constraint)

Posted on 2009-07-09
11
740 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
0
Comment
Question by:Milton
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24814623
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.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24814628
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24814643
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.
0
 
LVL 4

Author Comment

by:Milton
ID: 24814731
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
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24814759
If you see in the sample Inserts provided "Carbon tetrachloride" repeats twice in both the tables.
Kindly check that record and fix it.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24814778
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.
0
 
LVL 4

Author Comment

by:Milton
ID: 24814940
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

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 50 total points
ID: 24814987
Then you could have just done this:

SELECT *
INTO b
FROM a;

Or:
-- with no auto increment on id column in b
DELETE FROM b;

INSERT INTO b
SELECT * FROM a;
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 450 total points
ID: 24814992
>> 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

Just tried to find out the error record by running the insert scripts you provided in my machine.
And found what's wrong with the data since syntax is correct.
0
 
LVL 4

Author Closing Comment

by:Milton
ID: 31601650
Thank you all
0
 
LVL 4

Author Comment

by:Milton
ID: 24815110
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
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

910 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

21 Experts available now in Live!

Get 1:1 Help Now