CODEWITHSUNDEEP
c++sql-serverodbc
Jonathan Wood
Jonathan Wood

Reputation: 67283

Getting 'Cannot insert duplicate key' from UPDATE query

I'm writing an ODBC class to connect to a remote SQL Server database. I have most of it working.

The class has the ability to generate queries such as the following:

UPDATE Customers SET Id=?,Name=?,TaxId=?,ContactFName=?,ContactLName=?,Phone_Office=?,Phone_Mobile=?,Phone_Home=?,Email=?,Website=?,Address1_Physical=?,Address2_Physical=?,City_Physical=?,State_Physical=?,Zip_Physical=?,Address1_Billing=?,Address2_Billing=?,City_Billing=?,State_Billing=?,Zip_Billing=?,StartingBalance=?,Discount=?,BillingSequence=?,BillingCategory=?,ShowOnReport=?,Active=?,CreateDate=?

As you can see, it's an UPDATE query. Yet, running this query gives me an error:

Microsoft ODBC Driver 17 for SQL Server (SQL Server) : ReturnCode: -1 : Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (82). (State: 23000, NativeError: 2627) : The statement has been terminated. (State: 01000, NativeError: 3621)

I'm confused why I'm getting an error about inserting when I'm doing an update. Has anyone seen this?

Notes:

Upvotes: 0

Views: 3542

Answers (3)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89341

That is the message you should expect when an UPDATE statement violates a primary key. EG

use tempdb
go
drop table if exists t

create table t(id int primary key)

insert into t(id) values (1),(2)

go

update t set id = 2 where id = 1
--Msg 2627, Level 14, State 1, Line 11
--Violation of PRIMARY KEY constraint 'PK__t__3213E83F127C5D76'. Cannot insert duplicate key in object 'dbo.t'. The duplicate key value is (2).
--The statement has been terminated.

Upvotes: 1

pmbAustin
pmbAustin

Reputation: 3980

There's no WHERE clause on the UPDATE statement, so it's trying to update EVERY SINGLE ROW in the database, and since ID is one of the columns being changed, it's trying to set every row's ID to the same value. This is resulting in an attempt to create a duplicate primary key.

Make sure your UPDATE statement has an appropriate WHERE clause... like "WHERE ID = ?"... and it's probably best practice to NOT include the ID in that UPDATE statement if it's not changing.

Upvotes: 9

HereGoes
HereGoes

Reputation: 1320

In the UPDATE I see a field called ID. If you are making a change to the ID and it's the primary key then the DBMS will fuss because you are trying to store duplicate keys.

Upvotes: 0

Related Questions