Re: [sqlite] How to circumvent UNIQUE constraint

[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 desktops values (new row with index = 1);
>
> from the docs, which i hope i'm not misreading:
>
> http://sqlite.org/lang_expr.html#castexpr
> "When casting a TEXT value to INTEGER, the longest possible prefix of the
> value that can be interpreted as an integer number is extracted from the
> TEXT value and the remainder ignored."
>
> has worked for me for years but ymmv
>

​It looks very promising. Would also be very useful when the order is
changed. One strange quirk: the first statement does not work in
sqlitebrowser, but the second does. The first does work in the commandline
tool.

When I am building my GUI I am going to play with it.


The program I am using this table:

https://github.com/CecilWesterhof/PythonScripts/blob/master/startPrograms.py
​


-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 misreading:

http://sqlite.org/lang_expr.html#castexpr
"When casting a TEXT value to INTEGER, the longest possible prefix of the
value that can be interpreted as an integer number is extracted from the
TEXT value and the remainder ignored."

has worked for me for years but ymmv


On Mon, Jan 23, 2017 at 11:58 AM, Cecil Westerhof 
wrote:

> 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 ignore_check_constraints = ON
> >
> > A UNIQUE constraint is not a CHECK constraint.
> >
>
> ​But it works.
>
> --
> 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

Re: [sqlite] How to circumvent UNIQUE constraint

[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 ignore_check_constraints = ON
>
> A UNIQUE constraint is not a CHECK constraint.
>

​But it works.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 preferred, but
it should avoid the problem. Assumes you aren't using negative primary keys
or foreign keys...

On Jan 23, 2017 8:54 AM, "Clemens Ladisch"  wrote:

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
___
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

Re: [sqlite] How to circumvent UNIQUE constraint

[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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 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?
>

​It is actually quite simple:
PRAGMA ignore_check_constraints = ON
;
UPDATE desktops
SET indexNo = indexNo + 1
;
PRAGMA ignore_check_constraints = OFF
;
​

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 indexNo = -indexNo WHERE indexNo < 0;
>
> unless
>
> FOREIGN KEY indexNo references foo(bar)
> or
> FOREIGN KEY bar references desktops(indexNo)
>

​In this case it is only used to determine the order of the records.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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   TEXTNOT 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?
>>
>
> My favourite way (only needed in SQLite as this will work in most other
> DBs):
>
> 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;
>
> With X being the indexNo at which you wish to Insert the new row.
>

​I did this. Works. But I need to write some logic, because in the near
future I shall also need to reorder the records.​




> I like this because it's simple, quick, and always works without the need
> to calculate anything. If this table is really big (millions of rows) it
> /might/ be faster to just drop and recreate the index, you should test the
> time difference.


​Well, at the moment it are nine records and I do not think it will grow
much. When in another instance it will, I will look into the performance.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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: UNIQUE constraint failed: desktops.indexNo
>
> It should work.  It does work in other DBMSs, but it doesn't in
> SQLite.  It is a failure of atomicity in SQLite semantics.
>

​That is what I thought. It is to long ago that I did things like that, so
I was not sure.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 
> data.

Or make the field REAL instead of INTEGER.  Then you can insert a new row 
'between' any two existing rows by taking the mean of their two values.  Well, 
down to the resolution of REAL, of course.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 ... ;
> > UPDATE desktops SET indexNo = -indexNo WHERE indexNo < 0;
 
> unless
> 
>   FOREIGN KEY indexNo references foo(bar)

In this case changing the overloaded relative-record-number indexNo would not 
work at all, even if the update were atomic.

> or
>   FOREIGN KEY bar references desktops(indexNo)

ON UPDATE CASCADE would fix this, of course.
 
> I don't think enough functionality is exposed to create a generalized
> function that would just do the right thing.  One can imagine a
> C function sqlite3_exec_update that
> 
> 1.  determines the affected columns
> 2.  finds any applicable constraints
> 3.  drops the constraints
> 4.  begins a transaction
> 5.  executes the update
> 6.  re-adds the contraints
> 7.  commits
> 
> But just for starters ALTER TABLE does not support constraints,
> and SQLITE_MASTER doesn't reflect constraint definitions.
> 
> DRH suggests renaming the table or using an index instead.  I'm not
> sure renaming the table works in the presence of foreign key
> enforcement (so that would have to be touched, too).  Even if
> indexes are used, the index definitions are not exposed in a way that
> the could be dropped and re-created under programatic control without
> parsing the SQL.  Both approaches are inefficient if only a small
> proportion of rows are affected.  Both impose unnecessary complexity on
> the user.
> 
> The only place all the above information is readily available is inside
> the SQLite engine.  There the SQL is parsed and all applicable
> constraints are exposed in binary form.  A simplistic decision was made
> early on to enforce constraints on a row-by-row basis.  That decision
> was defensible at the time.  As SQLite has grown in sophistication --
> WAL, foreign keys, CTE, recursion -- lack of atomic update looms
> more and more as an important defect.

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 data.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 other SQL implementations where you can state at
> which point the constraints are enforced.

Every other SQL DBMS I'm aware of handles things like  primary key
constraint and unique constraint correctly out of the box.  Any
constraint that affects only one table can be enforced atomically, i.e.
after each SQL statement.  

You need deferred constraint enforcement for things that can't be
expressed atomically in SQL.  For example, assume two tables,
orders and order_items, with two rules:

1. every order must have at least one order_item 
2. every order_item must belong to an order

In creating a new order, these requirements are impossible to fill
simultateously, because INSERT affects only one table.  A workaround
like a permanent faux_item introduces needless compexity.  Deferred
constraint enforcement can apply contraints after both inserts.  

In Tutorial-D, Date uses a comma-operator to chain database updates
together, to indicate that the combination is atomic.  

IMO deferred constraints are way outside the scope of SQLite.  It's
complex.  Simple type enforcement and correct constraint enforcement
would serve users better.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 references foo(bar)
or
FOREIGN KEY bar references desktops(indexNo)

I don't think enough functionality is exposed to create a generalized
function that would just do the right thing.  One can imagine a 
C function sqlite3_exec_update that 

1.  determines the affected columns
2.  finds any applicable constraints
3.  drops the constraints
4.  begins a transaction
5.  executes the update
6.  re-adds the contraints
7.  commits

But just for starters ALTER TABLE does not support constraints,
and SQLITE_MASTER doesn't reflect constraint definitions.  

DRH suggests renaming the table or using an index instead.  I'm not
sure renaming the table works in the presence of foreign key
enforcement (so that would have to be touched, too).  Even if
indexes are used, the index definitions are not exposed in a way that
the could be dropped and re-created under programatic control without
parsing the SQL.  Both approaches are inefficient if only a small
proportion of rows are affected.  Both impose unnecessary complexity on
the user.  

The only place all the above information is readily available is inside
the SQLite engine.  There the SQL is parsed and all applicable
constraints are exposed in binary form.  A simplistic decision was made
early on to enforce constraints on a row-by-row basis.  That decision
was defensible at the time.  As SQLite has grown in sophistication --
WAL, foreign keys, CTE, recursion -- lack of atomic update looms
more and more as an important defect.  

--jkl




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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,
>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?

Considering there is no constraint on indexNo with respect to
negative or zero values, I would suggest:

INSERT INTO desktops (name,indexNo,value,waitSeconds) 
VALUES ('thename',(SELECT min(indexNo) FROM desktops) - 1,
'thevalue',thewaitseconds);

-- 
Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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: desktops.indexNo
> 
> It should work.  It does work in other DBMSs, but it doesn't in
> SQLite.  It is a failure of atomicity in SQLite semantics.

This is one I do feel is a bug in SQLite.  The command

>> UPDATE desktops SET indexNo = indexNo  + 1

can lead to violations of the UNIQUE constraint but whether it does or not is 
an implementation detail (depends which order the rows are processed) and not 
under user-control.  So the proper requirement is that the UNIQUE check be made 
at the end of the transaction.  And at the end of the transaction there would 
be no violations, no matter in which order the SQL engine chose to process rows.

Unfortunately changing SQLite to check the constraints at the end of the 
transaction rather than as each change is made would require a lot of 
programming.  Maybe it’s one for SQLite4.

I think I’ve seen other SQL implementations where you can state at which point 
the constraints are enforced.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 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?


My favourite way (only needed in SQLite as this will work in most other 
DBs):


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;

With X being the indexNo at which you wish to Insert the new row.

I like this because it's simple, quick, and always works without the 
need to calculate anything. If this table is really big (millions of 
rows) it /might/ be faster to just drop and recreate the index, you 
should test the time difference.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 DBMSs, but it doesn't in
SQLite.  It is a failure of atomicity in SQLite semantics.  

As DRH mentions, one workaround is to drop the constraint
temporarily.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[Tony Papadimitriou]

Here's one possibility (simplified table for example):

create table desktops(
 indexno integer not null unique,
 nametextnot 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(
   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 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

Re: [sqlite] How to circumvent UNIQUE constraint

[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 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?

BEGIN;
ALTER TABLE desktops RENAME TO desktops_old;
CREATE TABLE desktops(
   nameTEXTNOT NULL PRIMARY KEY,
   indexNo INTEGER NOT NULL UNIQUE,
   value   TEXTNOT NULL UNIQUE,
   waitSeconds INTEGER NOT NULL
);
INSERT INTO desktops SELECT name, indexNo+1, value, waitSeconds
  FROM desktops_old;
DROP TABLE desktops_old;
COMMIT;

The above is just the first method that comes to mind.  There are
certainly others.  For example, you might enforce the uniqueness of
indexNo with a separate UNIQUE index, then simply DROP the index
before the update and recreate it afterwards.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to circumvent UNIQUE constraint

[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 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?
> 

I don't think this will work in a single SQL statement. If you start
with the largest value of indexNo and work in descending order, it
should work. However, this would typically be done in a procedural loop
where you can depend on the ordering of a cursor.

Maybe somebody knows a clever SQL trick to do it in a single statement?
The problem is that you could build a subquery to return the "hole",
i.e. the next indexNo to update, but you cannot modify the same table
which is used in a subquery of the same UPDATE statement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to circumvent UNIQUE constraint

[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 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