Re: [sqlite] How to circumvent UNIQUE constraint
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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