Here's one possibility (simplified table for example):
create table desktops(
indexno integer not null unique,
name text not null primary key
);
insert into desktops values
(1,'CompA'),
(2,'CompB'),
-- we want to insert new record here bumping all above by one
(3,'CompD'),
(4,'CompE');
select * from desktops order by indexno; --BEFORE
-- Assuming largest indexno is initially N (in this example 4)
-- Add N+1 (or N+x where x > 0) to all records over and including
the one spot you want freed (in this example 3)
-- Subtract N from all over N+1 you added previously
-- Insert the new record into the now empty slot
begin;
update desktops set indexno = indexno + 5 where indexno >=3;
update desktops set indexno = indexno - 4 where indexno > 5;
insert into desktops values(3,'CompC');
end;
select * from desktops order by indexno; --AFTER
-----Original Message-----
From: Cecil Westerhof
Sent: Saturday, January 21, 2017 12:54 PM
To: SQLite mailing list
Subject: [sqlite] How to circumvent UNIQUE constraint
I have the following (work in progress) table:
CREATE TABLE desktops(
name TEXT NOT NULL PRIMARY KEY,
indexNo INTEGER NOT NULL UNIQUE,
value TEXT NOT NULL UNIQUE,
waitSeconds INTEGER NOT NULL
);
I want to insert a record in front of the others, so indexNo has to be
increased with one for all records. I would think that this would work:
UPDATE desktops
SET indexNo = indexNo + 1
But it does not, it gives:
Error: UNIQUE constraint failed: desktops.indexNo
How can I make this work?
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
