Update a primary key constraint

Question

Update a primary key constraint

CooLDBA

Hall of Fame

Points: 3301
More actions
March 31, 2005 at 7:20 pm

#164060

I have two table...
the first table, I can update the primary key but the other table I can't.
error msg: Violation of PRIMARY KEY constraint 'PK_Customer_Payors_1__14'. Cannot insert duplicate key in object 'Customer_Payors'.
The statement has been terminated.
SELECT ObjectProperty(object_id('PK_History'),
'CnstIsDisabled') = 0
SELECT ObjectProperty(object_id('PK_Customer'),
'CnstIsDisabled') = 0
any ideas?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Aaron Templeton SSCarpal Tunnel Points: 4655 More actions · Answer 1

I think a little more information is needed. Are you directly updating Customer_Payors or is there some sort of relationship being applied that is causing Customer_Payors to be updated indirectly? What are you trying to update the key to? A constant, or a formula? Is there a where clause involved? Based on the select statements you provided, are you trying to tell us that the primary key on those two tables has been disabled, or are you actually trying to disable them via the ObjectProperty function which by my understanding is just a retrieval method, not something that updates a property? If you are updating the key to some constant value for a specific row, have you queried the table to report all rows with a key value matching what you are attempting to convert it to?

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

The problem is that, whatever value you are trying to update with, already exists in the Primary Key. You cannot have dupes in the Primary Key (sorry if that's so obvious but some just don't know what a Primary Key does).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)