Key Violation

Question

Key Violation

scking

SSCrazy

Points: 2598
More actions
March 18, 2004 at 11:54 am

#359306

I'm changing a Part Number and Cage code, which comprise a concatenated key field, using cascading updates and getting an error. The cascading updates should, in turn, update the PN/Cage for the item in the Alternates table but, instead, announces a key violation and rolls back.
The T-SQL looks to find if the new PN/Cage already exists. If it does it performs some table updates using T-SQL. This works fine. When the PN/Cage doesn't exist we change the PN/Cage from the @OldPN to the @NewPN and Cage. The debugger drops into the ELSE part of the statement and then I get the violation notice.
IF EXISTS (SELECT * FROM tblPN WHERE tblPN.PN=@NewPN AND tblPN.Cage=@NewCage)
    -- DO THIS STUFF
ELSE
  /* * RENAME IT AND UPDATE THE TIMESTAMP FIELD */
         UPDATE p
        SET p.PN=@NewPN, p.Cage=@NewCage
         FROM tblPN p
        WHERE p.PN = @OldPN And p.Cage = @OldCage
RETURN
Any assistance would be appreciated.
Steve

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

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Hmmm... Let me get a better idea,

First Point : So here tblPN and p are two different tables both having PN and Cage as columns and you are trying to check if combination of PN and Cage exists in tblPN and trying to update same columns on table p with the new values when the above query returns false and the update statement on table p is failing on unique key violation right, so what I am wondering is basically is the unique key constraint on combination of PN and Cage together or on each one of them?

And if the two tables are differnent then why don't you run the exists query on the table on which you want to update the records?

Sorry if I am missing anything.

Thanks

Prasad Bhogadi
www.inforaise.com

noeld SSC Guru Points: 96590 More actions · Answer 2

change the Update Statement to :

UPDATE tblPN

SET <A href="mailtoN=@NewPN">PN=@NewPN, Cage=@NewCage

WHERE tblPN.PN = @OldPN And tblPN.Cage = @OldCage

* Noel

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Hi Noel,

Yeah this works fine with the assumption that the constraint is on the combination of the two columns PN and Cage, since the exists query is checking with a 'AND' clause.

Prasad Bhogadi
www.inforaise.com