On Tue, 2007-09-25 at 10:06 +0530, Anoo Sivadasan Pillai wrote:
But since you can’t control the order, it is likely that the operation will sometimes and will succeed sometimes, with the same data.. Weird I feel .
Not so weird (IMO). Many databases will return rows unordered unless you specify otherwise. Once one understands that uniqueness constraints are evaluated immediately and that rows are visited unordered, it's not so weird.
The work around given by you will work, Does it mean that whenever we are updating a unique key we have to take this precaution ?
Probably. I suspect that you're using unique or primary keys to imply some meaning that would be better stored elsewhere, and definitely NOT in a primary key. Perhaps you should consider refactoring your design. You're doing something wrong if you find that you need to update primary keys routinely.
-Reece
