On Fri, 6 Sep 2013 17:45:59 -0400 (EDT)
j.merr...@enlyton.com wrote:
> I propose that you remove the unique index because SQLite does not
> handle the update case the way you want.
The correct general approach, in light of observed behavior,
1. begin IMMEDIATE transaction
2. select rows into a temporary table
3. update temporary table
4. delete from main table
5. insert into main table from temporary table
6. commit
An alternative in this case would be to expand the unique constraint
with another column to support updates. Something like this:
create table t
( PKey INTEGER PRIMARY KEY
, Name TEXT
, Sequence INTEGER
, _pending INTEGER default 0 check (_pending in (PKey, 0))
, unique (Name, Sequence, _pending)
);
insert into t (Name, Sequence) values ('Blue', 1);
insert into t (Name, Sequence) values ('Blue', 2);
insert into t (Name, Sequence) values ('Blue', 3);
insert into t (Name, Sequence) values ('Blue', 4);
insert into t (Name, Sequence) values ('Blue', 5);
-- insert a new 3
BEGIN TRANSACTION ;
UPDATE t
set _pending = PKey where Sequence >=3;
UPDATE t
SET Sequence = Sequence + 1
WHERE Sequence >= 3
AND Name = 'Blue';
UPDATE t set _pending = 0;
insert into t (Name, Sequence) values ('Blue', 3);
COMMIT;
select * from t;
PKey Name Sequence _pending
---------- ---------- ---------- ----------
1 Blue 1 0
2 Blue 2 0
3 Blue 4 0
4 Blue 5 0
5 Blue 6 0
6 Blue 3 0
That has the effect of voiding the unique constraint while _pending is
nonzero, but leaving it in force after the commit.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
