How to circumvent UNIQUE constraint

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

How to circumvent UNIQUE constraint

Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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(
>     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?
>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Richard Hipp-3
In reply to this post by Cecil Westerhof-5
On 1/21/17, Cecil Westerhof <[hidden email]> wrote:

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

BEGIN;
ALTER TABLE desktops RENAME TO desktops_old;
CREATE TABLE desktops(
   name        TEXT    NOT NULL PRIMARY KEY,
   indexNo     INTEGER NOT NULL UNIQUE,
   value       TEXT    NOT 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Tony Papadimitriou
In reply to this post by Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

James K. Lowden
In reply to this post by Cecil Westerhof-5
On Sat, 21 Jan 2017 11:54:57 +0100
Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

R Smith
In reply to this post by Cecil Westerhof-5


On 2017/01/21 12:54 PM, Cecil Westerhof wrote:

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

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Simon Slavin-3
In reply to this post by James K. Lowden

On 21 Jan 2017, at 4:32pm, James K. Lowden <[hidden email]> wrote:

> Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Kees Nuyt
In reply to this post by Cecil Westerhof-5
On Sat, 21 Jan 2017 11:54:57 +0100, Cecil Westerhof
<[hidden email]> wrote:

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

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

James K. Lowden
In reply to this post by R Smith
On Sat, 21 Jan 2017 19:33:06 +0200
R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

James K. Lowden
In reply to this post by Simon Slavin-3
On Sat, 21 Jan 2017 18:14:06 +0000
Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Keith Medcalf
In reply to this post by James K. Lowden


On Saturday, 21 January, 2017 13:45 James K. Lowden <[hidden email]> wrote:
> On Sat, 21 Jan 2017 19:33:06 +0200
> R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Simon Slavin-3

On 22 Jan 2017, at 2:40am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Cecil Westerhof-5
In reply to this post by James K. Lowden
2017-01-21 17:32 GMT+01:00 James K. Lowden <[hidden email]>:

> On Sat, 21 Jan 2017 11:54:57 +0100
> Cecil Westerhof <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Cecil Westerhof-5
In reply to this post by R Smith
2017-01-21 18:33 GMT+01:00 R Smith <[hidden email]>:

>
>
> On 2017/01/21 12:54 PM, Cecil Westerhof wrote:
>
>> 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?
>>
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Cecil Westerhof-5
In reply to this post by James K. Lowden
2017-01-21 21:44 GMT+01:00 James K. Lowden <[hidden email]>:

> On Sat, 21 Jan 2017 19:33:06 +0200
> R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Cecil Westerhof-5
In reply to this post by Cecil Westerhof-5
2017-01-21 11:54 GMT+01:00 Cecil Westerhof <[hidden email]>:

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

​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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Scott Robison-2
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" <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to circumvent UNIQUE constraint

Cecil Westerhof-5
In reply to this post by Clemens Ladisch
2017-01-23 16:53 GMT+01:00 Clemens Ladisch <[hidden email]>:

> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]>
wrote:

> 2017-01-23 16:53 GMT+01:00 Clemens Ladisch <[hidden email]>:
>
> > 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12