workaround for your problem :
create table t1 (pk integer primary key, name text, seq integer) ;
create unique index idxt1 on t1 (name,seq) ;
insert into t1 values (1, 'blue', 1) ;
insert into t1 values (2, 'blue', 2) ;
insert into t1 values (3, 'blue', 3) ;
insert into t1 values (4, 'blue', 4) ;
insert into t1 values (5, 'blue', 5) ;
before updating your unique index just drop and recreate it !
Behaviour till now :
sqlite> select *from t1 ;
1|blue|1
2|blue|2
3|blue|3
4|blue|4
5|blue|5
sqlite> update t1 set seq=seq+1 where pk in (select pk from t1 where seq
>= 3 order by seq desc) ;
Error: columns name, seq are not unique
Behaviour with the workaround :
drop index idxt1 ;
sqlite> update t1 set seq=seq+1 where seq >= 3 ;
sqlite> create unique index idxt1 on t1(name,seq) ;
sqlite> select * from t1 ;
sqlite> insert into t1 (name, seq) values ('blue',3) ;
sqlite> select * from t1 ;
1|blue|1
2|blue|2
6|blue|3
3|blue|4
4|blue|5
5|blue|6
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
