We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
8 days, 14 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Cannot update table (Violation of PRIMARY KEY constraint)
Posted on 2009-07-09
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
3- +1
11 Comments
Active today
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.
Active 3 days ago
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
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
Active 1 day ago
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.
Fix it out in the INSERT statements to get it corrected.
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
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
Active 1 day ago
If you see in the sample Inserts provided "Carbon tetrachloride" repeats twice in both the tables.
Kindly check that record and fix it.
Kindly check that record and fix it.
Active today
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.
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
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
Active today
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;
SELECT *
INTO b
FROM a;
Or:
-- with no auto increment on id column in b
DELETE FROM b;
INSERT INTO b
SELECT * FROM a;
Active 1 day ago
>> 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.
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.
Featured Post
Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 14 hours left to enroll
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.