Re: Updating a unique constrant

On Fri, Jun 04, 2004 at 13:09:40 -0600,
Peter Rahm-Coffey <RahmCoff(at)radio1190(dot)org> wrote:
>
> If current_play is less than 94, I get uniqueness error. Since the update
> is most likely to be done when playorder = 1, this is a problem. I
> switched to the slower numeric to try doing insert values (1+1/123), but
> then I can't do SELECT ... WHERE playorder = 1.00813008130081. (It returns
> false.) Is there a way out of my problem without removing the unique index
> and the foreign keys that use it?

Uniqueness constraints can't be deferred. One common strategy is to change
all of the values to values in a set disjoint from what they are now union
with what they will be. (Commonly people make the negative of their
current value.) And then change them to the correct new values.