***BOGO*** Re: increment contiguous unique values

[Kevin Hunter <hunteke@xxxxxxxxxxx>]

At 3:15p -0400 on Tue, 27 May 2008, Jack Bates wrote:
> I can only guess that MySQL is incrementing the second row before the
> third row, resulting in a temporarily duplicate value 3 in the foo
> column in the second and third row, and reporting an error?
> 
> I do want MySQL to prevent duplicate values in the foo column, however
> my UPDATE statement should not result in duplicate values. Is there any
> way to defer this constraint until after the statement completes?
> 
> I am using InnoDB...

To strictly answer your question, I believe the answer is no.  Check the
docs, but I don't think InnoDB currently has the ability defer
constraints.  The "standard" MySQL behavior checks constraints
row-by-row, and InnoDB follows suit.  Frustrating because the SQL spec
says that bulk operations *should* defer constraints unless told otherwise.

To solve your problem, does the UPDATE ... ORDER BY syntax not achieve
what you need?  I believe that syntax has been around for a while.

Good luck,

Kevin

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx