List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 14 1999 4:52pm
Subject:Re: Updating (incrementing) primary keys
View as plain text  
Kristian Köhntopp wrote:
> 
> I have a column "L integer", which also is primary key in that
> table. I need to increment all values of L over a certain limit
> by 2 to make room for an inserted element.
> 
> update t set L=L+2 where L>5;
> 
> fails with
> 
> ERROR 1022: Can't write, duplicate key in table 't'
> 
> because Mysql performs the operation in ascending or arbitrary
> order. If Mysql performed this operation in decending order or
> checked the key contraint only after performing the entire
> update, all would be completey legal.
> 
> As things are, I have to drop the primary key and recreate it
> after the insert. This is slow.
> 
> How would I approach this problem more efficiently?
> 
> Kristian
> 
> --
> Kristian Köhntopp, NetUSE Kommunikationstechnologie GmbH
> Siemenswall, D-24107 Kiel, Germany, +49 431 386 436 00
> Using PHP3? See our web development library at
> http://phplib.shonline.de/ (GPL)
> 

Do you really need that field to be a primary key? How
about just having an index on it?

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
Updating (incrementing) primary keysKristian Köhntopp14 May
  • Re: Updating (incrementing) primary keysChristian Mack14 May
  • Re: Updating (incrementing) primary keysKristian Köhntopp14 May
  • Updating (incrementing) primary keysMichael Widenius14 May
  • Re: Updating (incrementing) primary keysSasha Pachev14 May