Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-25 Thread Cecil Westerhof
2017-01-23 23:20 GMT+01:00 Ben Newberg : > what i've done in the past is append a character to the value and make use > of "cast": > > ​​ > update desktops set indexNo = indexNo || '_'; > update desktops set indexNo = cast(indexNo as integer) + 1; > > then: > insert into

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Ben Newberg
what i've done in the past is append a character to the value and make use of "cast": update desktops set indexNo = indexNo || '_'; update desktops set indexNo = cast(indexNo as integer) + 1; then: insert into desktops values (new row with index = 1); from the docs, which i hope i'm not

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Cecil Westerhof
2017-01-23 16:53 GMT+01:00 Clemens Ladisch : > Cecil Westerhof wrote: > >> UPDATE desktops > >> SET indexNo = indexNo + 1 > >> > >> But it does not, it gives: > >> Error: UNIQUE constraint failed: desktops.indexNo > > > > ​It is actually quite simple: > > PRAGMA

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Scott Robison
This might be helpful. Maybe not. It's not an answer to the exact question, but ... What if you were to set all the IDs to their negative, then update them as desired? UPDATE TABLEA SET ID = -ID; UPDATE TABLEA SET ID = -ID + 1; Or something like that. It is not as efficient as would be

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Clemens Ladisch
Cecil Westerhof wrote: >> UPDATE desktops >> SET indexNo = indexNo + 1 >> >> But it does not, it gives: >> Error: UNIQUE constraint failed: desktops.indexNo > > ​It is actually quite simple: > PRAGMA ignore_check_constraints = ON A UNIQUE constraint is not a CHECK constraint. Regards, Clemens

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Cecil Westerhof
2017-01-21 11:54 GMT+01:00 Cecil Westerhof : > I have the following (work in progress) table: > CREATE TABLE desktops( > nameTEXTNOT NULL PRIMARY KEY, > indexNo INTEGER NOT NULL UNIQUE, > value TEXTNOT NULL UNIQUE, > waitSeconds

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Cecil Westerhof
2017-01-21 21:44 GMT+01:00 James K. Lowden : > On Sat, 21 Jan 2017 19:33:06 +0200 > R Smith wrote: > > > UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; > > INSERT INTO desktops ... new row for indexNo X ... ; > > UPDATE desktops SET

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Cecil Westerhof
2017-01-21 18:33 GMT+01:00 R Smith : > > > On 2017/01/21 12:54 PM, Cecil Westerhof wrote: > >> I have the following (work in progress) table: >> CREATE TABLE desktops( >> nameTEXTNOT NULL PRIMARY KEY, >> indexNo INTEGER NOT NULL UNIQUE, >> value

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-23 Thread Cecil Westerhof
2017-01-21 17:32 GMT+01:00 James K. Lowden : > On Sat, 21 Jan 2017 11:54:57 +0100 > Cecil Westerhof wrote: > > > I would think that this would work: > > UPDATE desktops > > SET indexNo = indexNo + 1 > > > > But it does not, it gives: > > Error:

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Simon Slavin
On 22 Jan 2017, at 2:40am, Keith Medcalf wrote: > My suggestion would be to forgo the artificial relative position being > computed by the application and replace it with the actual data used to > determine the ordering, and add an appropriate ORDER BY when retrieving the

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Keith Medcalf
On Saturday, 21 January, 2017 13:45 James K. Lowden wrote: > On Sat, 21 Jan 2017 19:33:06 +0200 > R Smith wrote: > > UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; > > INSERT INTO desktops ... new row for indexNo X ... ; > >

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread James K. Lowden
On Sat, 21 Jan 2017 18:14:06 + Simon Slavin wrote: > It is a failure of atomicity in SQLite semantics. > > This is one I do feel is a bug in SQLite. Thank you for your support. I feel it's important to understand it's a bug, not a feature. > I think I?ve seen

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread James K. Lowden
On Sat, 21 Jan 2017 19:33:06 +0200 R Smith wrote: > UPDATE desktops SET indexNo = -indexNo - 1 WHERE indexNo >= X; > INSERT INTO desktops ... new row for indexNo X ... ; > UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0; unless FOREIGN KEY indexNo

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Kees Nuyt
On Sat, 21 Jan 2017 11:54:57 +0100, Cecil Westerhof wrote: >I have the following (work in progress) table: >CREATE TABLE desktops( >nameTEXTNOT NULL PRIMARY KEY, >indexNo INTEGER NOT NULL UNIQUE, >value TEXTNOT NULL UNIQUE, >

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Simon Slavin
On 21 Jan 2017, at 4:32pm, James K. Lowden wrote: > Cecil Westerhof wrote: > >> I would think that this would work: >> UPDATE desktops >> SET indexNo = indexNo + 1 >> >> But it does not, it gives: >> Error: UNIQUE constraint failed:

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread R Smith
On 2017/01/21 12:54 PM, Cecil Westerhof wrote: I have the following (work in progress) table: CREATE TABLE desktops( nameTEXTNOT NULL PRIMARY KEY, indexNo INTEGER NOT NULL UNIQUE, value TEXTNOT NULL UNIQUE, waitSeconds INTEGER NOT NULL ); ​I want

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread James K. Lowden
On Sat, 21 Jan 2017 11:54:57 +0100 Cecil Westerhof wrote: > 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 It should work. It does work in other

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Tony Papadimitriou
l 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( nameTEXTNOT NULL PRIMARY KEY, indexNo INTEGER

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Richard Hipp
On 1/21/17, Cecil Westerhof wrote: > I have the following (work in progress) table: > CREATE TABLE desktops( > nameTEXTNOT NULL PRIMARY KEY, > indexNo INTEGER NOT NULL UNIQUE, > value TEXTNOT NULL UNIQUE, > waitSeconds INTEGER NOT

Re: [sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Robert Hairgrove
On Sat, 2017-01-21 at 11:54 +0100, Cecil Westerhof wrote: > I have the following (work in progress) table: > CREATE  TABLE desktops( > nameTEXTNOT NULL PRIMARY KEY, > indexNo INTEGER NOT NULL UNIQUE, > value   TEXTNOT NULL UNIQUE, > waitSeconds INTEGER NOT

[sqlite] How to circumvent UNIQUE constraint

2017-01-21 Thread Cecil Westerhof
I have the following (work in progress) table: CREATE TABLE desktops( nameTEXTNOT NULL PRIMARY KEY, indexNo INTEGER NOT NULL UNIQUE, value TEXTNOT NULL UNIQUE, waitSeconds INTEGER NOT NULL ); ​I want to insert a record in front of​ the others, so indexNo