Primary key violation during UPDATE statement

Why am I receiving the following error in a stored procedure that was called from an after insert trigger?

   Server: Msg 2627, Level 14, State 1, Procedure usp_ReplicateFoo, line 254
   Violation of PRIMARY KEY constraint 'PK_foo'. Cannot insert duplicate key in object 'foo'.
   The statement has been terminated.

The purpose of my trigger is that I have to keep certain columns of a table from another database in sync with my table in the current database.  In my AFTER INSERT trigger I call a stored procedure that determines what type of change is made on the local table and then calls INSERT or UPDATE procedures as needed.  This problem manifests itself when my UPDATE procedure includes a primary key change.  It turns out that occasionally there is an existing record in the target table with a matching primary key so I added code to DELETE that record before the UPDATE executes.  A SELECT statement in the procedure confirms that the prior record is gone but I still get the PRIMARY KEY constraint violation!

One variation that I've tried is after the DELETE occurs, if the PRIMARY KEY is changing then execute an INSERT followed by a DELETE of the previous record.  If no PRIMARY KEY change is occuring then I just do the UPDATE statement which achieves the results that I need.

My question is, why can't I just do the UPDATE after DELETING a conflicting PRIMARY KEY first?  This INSERT/DELETE logic seems unnecessary.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>This problem manifests itself when my UPDATE procedure includes a primary key change.  I
then you choose a bad primary key. primary key values should NEVER change.

Add a begin transaction before the delete, then a commit transaction after.  The transaction may not be committing until after all the statements are ran.
Vadim RappCommented:
You should be able to UPDATE. here's a sample code that I created that does the same thing as yours, and it works without a problem. There must be something else in your trigger that breaks it.

create table t(id int not null primary key,c char(1))
insert into t(id,c) select 1,'a'
insert into t(id,c) select 2,'b'
create trigger t_i on t for insert as
delete from t where id=1
update t set id=1 where id=2
insert into t select 3,'c'
select * from t          --- correctly returns 'b' in the first row, i.e. the 1st row was deleted and the 2nd row was updated

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Hi JeffR,

Have you tried using the "GO" statement after the part that deletes the duplicate (the original one)? If might force SQL-Server to really delete the record, before it evaluates the rest of the stored procedure. I'm not sure if this suggestion solves the problem, but it's the only suggestion I have at this point.

I only partially agree with you. I think this fully depends on the database modelling approach you have. If you have a fully normalised database, then indeed, you will have only primary keys that consist of only one column that has no meaning whatsoever, except to keep the rows unique. But if one uses a different approach, in which one has a primary key over the column(s) that HAVE a meaning and would be unique in the real world, the data might change. Say one has an orders table and an orderdetails table. A primary key in OrderDetails could be on "ID" or "OrderDetailsID" using one approach, but could also be on both OrderNr and LineNr using another approach. I think Jeff uses the latter kind of approach in his database.

Anyway, I don't believe that his approach is what's causing the error. I'm sure you have some other suggestions about what is going on here...

Cheers and I hope this contributes in any way,

Luc Derckx
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the problem with using a value in the table for 2 things is always dangereous, because one day someone will change a meaning and/or ignore the other meaning.
one should always have a non-meaning primary key in the OLTP tables, where the natural identifying value can still serve as unique constraint/index etc
jeffrAuthor Commented:

I agree that the existing data model is not perfect.  It must have made sense to somebody at some point in the past.

Now back to the UPDATE problem that I'm troubled with:

The background is as follows:
Say, for example, the table stores an alphanumeric identifier as the primary key.  If customer 'A' goes bankrupt then their record is set to an inactive status.  Their record may become active again if company 'A' emerges from bankruptcy so we don't want to delete the data yet.  However company 'A' may never emerge from bankruptcy and we then want to reuse the alphanumeric identifier.

Then company 'B' comes along and they fit the criteria to use this inactive identifier.  However we set them up with a new identifier first.  Then company 'B' requests that we change their alphanumeric identifier to the one which was previously used by company 'A'.  So, in this stored proc we have a transaction that deletes the conflicting old record from company 'A' and then tries to UPDATE the alphanumeric identifier field of company 'B' which is the primary key of the table.  This fails.

Is this a problem that MSSQL has with updating PRIMARY KEY values?  I don't think so because if I update to a value that didn't have to be deleted in this transaction then the UPDATE works correctly.  This is happening in an uncommitted transaction.  For debugging I queried after the DELETE which showed me that the conflicting row was no longer there.  So the UPDATE should work, right?  But it does not.

Vadim RappCommented:
did you compare to the sample I posted?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Have you tried the "GO" statement in your stored procedure?
jeffrAuthor Commented:
It turned out that there was a problem with my data.  I implemented vadimrapp1's example but I altered it to be an update trigger as that is where I was having the problem.  When this worked it caused me to examine the issue more closely becaues it was arguably a simplified version of my code.

I had multiple records being picked up by my update statement due to poor test data in our development system.  It was vadimrapp1's example that inspired me to dig further into this.

Thanks everyone!

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.