Re: Unique index hassles

On Wed, 21 May 2003 20:47:49 +0100, "Richard Gration"
<richard(at)zync(dot)co(dot)uk> wrote:
>CREATE TABLE question
>( [...]
> pid INTEGER NOT NULL,
> order_val SMALLINT NOT NULL,
>);
>CREATE [UNIQUE] INDEX idx_question1 ON question (pid,order_val);
>
>UPDATE question SET order_val = order_val + 1 WHERE order_val > 1;
>ERROR: Cannot insert a duplicate key into unique index idx_question1

Can't comment on whether this is a bug, a flaw, or intended behaviour.
If you have an *implicit* constraint order_val > 0, then a workaround
would be

BEGIN;
UPDATE question SET order_val = - order_val
WHERE order_val > 1 [AND hopefully pid = something];
UPDATE question SET order_val = - order_val + 1
WHERE order_val < 0 [AND ...];
COMMIT;

Servus
Manfred