Re: [Firebird-devel] Update queries and unique indices...

[Adam G. <s30...@ya...>]

> It's about update queries, uniques indices and values used in the
> update...
> 
> To understand the first trick, first, create the table, the index,
> and add some values...
> 
> CREATE TABLE MYTABLE
> (MYKEY INTEGER NOT NULL);
> 
> CREATE UNIQUE INDEX IDX_MYKEY ON MYTABLE
>   (MYKEY);
> 
> COMMIT;
> 
> INSERT INTO MYTABLE (MYKEY) VALUES (1);
> INSERT INTO MYTABLE (MYKEY) VALUES (2);
> INSERT INTO MYTABLE (MYKEY) VALUES (3);
> INSERT INTO MYTABLE (MYKEY) VALUES (4);
> INSERT INTO MYTABLE (MYKEY) VALUES (5);
> INSERT INTO MYTABLE (MYKEY) VALUES (6);
> INSERT INTO MYTABLE (MYKEY) VALUES (7);
> INSERT INTO MYTABLE (MYKEY) VALUES (8);
> 
> Second, here is the problem :
> 
> UPDATE MYTABLE SET MYKEY = MYKEY + 1 WHERE MYKEY > 5;
> 
> > SQL Message : -803
> > Invalid insert or update value(s): object columns are
> > constrained - no 2 table rows can have duplicate column values
> 
> > Engine Code    : 335544349
> > Engine Message :
> > attempt to store duplicate value (visible to active transactions) in
> unique index "IDX_MYKEY"
> 
> 
> The Question is : why the engine doesn't wait the end of the update
> to check the constraints ? 'cause the workaround of Philippe is to
> remove the unique constraint (or to make a loop inside a SP)... and
> it's not satisfactory...  even if
> my program works great...

Another workaround would be to use EXECUTE BLOCK (FB2+) or a STORED
PROCEDURE (<FB2) to make sure the records are updated in an order that
would avoid the conflict.

eg

FOR
  SELECT MYKEY 
  FROM MYTABLE 
  WHERE MYKEY > 5
  ORDER BY MYKEY DESC 
INTO :MYKEY 
DO
BEGIN
  UPDATE MYTABLE SET MYKEY = :MYKEY + 1 WHERE MYKEY = :MYKEY;
END


Adam