Thread: Updating a unique constrant

I've having trouble with updating a unique index. Briefly:


CREATE TABLE playlist (
  [...]
  playorder numeric UNIQUE NOT NULL,
  [...]
};

CREATE TABLE globals (
  [...]
  current_play numeric NOT NULL REFERENCES playlist(playorder),
  [...]
);

INSERT INTO playlist(playorder) VALUES (1);
INSERT INTO playlist(playorder) VALUES (2);
INSERT INTO playlist(playorder) VALUES (3);
[...]
INSERT INTO playlist(playorder) VALUES (217);

UPDATE playlist SET playorder = playorder + 123 FROM globals WHERE
playorder > current_play;


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?

--
Peter Rahm-Coffey

On Fri, Jun 04, 2004 at 13:09:40 -0600,
  Peter Rahm-Coffey <RahmCoff@radio1190.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.

On Sun, 6 Jun 2004, Bruno Wolff III wrote:

> 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.

Thanks for the advice. I already screwed up my test database when tring to
make it work without the unique index.
--
Peter of the Norse