"Violation of PRIMARY KEY constraint" when no duplicate exists?

[embarkr]

I am getting the error:
"Violation of PRIMARY KEY constraint 'PK_tblCustomsTariffTreeMap'. Cannot insert duplicate key in object 'dbo.tblCustomsTariffCodeTreeMap'."

However, the record I am inserting does not represent a duplicate on the primary key.

To ensure this I ran the following and still got the error:

update tblCustomsTariffCodeTreeMap
set TariffCodeID = (SELECT max(tariffcodeid)+1 from tblCustomsTariffCodeTreeMap)
where TreeNodeID = 955

If I'm explicitly inserting a value which is 1 greater than the max value, I'm puzzled as to how it can be a duplicate...?

The primary key is defined as follows:

• index_name : PK_tblCustomsTariffTreeMap
  index_description : clustered, unique, primary key located on PRIMARY
  index_keys : TariffCodeID, TreeNodeID

Any suggestions?

[ck9663]

I am getting the error:
"Violation of PRIMARY KEY constraint 'PK_tblCustomsTariffTreeMap'. Cannot insert duplicate key in object 'dbo.tblCustomsTariffCodeTreeMap'."

However, the record I am inserting does not represent a duplicate on the primary key.

To ensure this I ran the following and still got the error:

update tblCustomsTariffCodeTreeMap
set TariffCodeID = (SELECT max(tariffcodeid)+1 from tblCustomsTariffCodeTreeMap)
where TreeNodeID = 955

If I'm explicitly inserting a value which is 1 greater than the max value, I'm puzzled as to how it can be a duplicate...?

The primary key is defined as follows:

• index_name : PK_tblCustomsTariffTreeMap
  index_description : clustered, unique, primary key located on PRIMARY
  index_keys : TariffCodeID, TreeNodeID

Any suggestions?

first the why...your query is an update and a select....your select will run first..so if the max+1, let's say is = 16...it's the same as doing:

update tblCustomsTariffCodeTreeMap
set TariffCodeID = 16
where TreeNodeID = 955

so if you have two or more records with TreeNodeID = 955, you'll experience this problem with PK violation....what are you trying to do on your table?

[embarkr]

first the why...your query is an update and a select....your select will run first..so if the max+1, let's say is = 16...it's the same as doing:

update tblCustomsTariffCodeTreeMap
set TariffCodeID = 16
where TreeNodeID = 955

so if you have two or more records with TreeNodeID = 955, you'll experience this problem with PK violation....what are you trying to do on your table?

Thank you ck9663. This is a sure sign that I've been staring at the screen too long! I was originally dealing with an INSERT which I changed to an UPDATE but did not modify the criteria properly. Then when I got the error I didn't see the obvious problem (a classic case of seeing what you expect to see!). thanks again - I'm back from lala land now.