I just thought of what may be a simpler solution.
I'm assuming that there is a certain limit to the length of the books
(positions can be safely assumed to never exceed say, 100,000)
So what can be done is
update page set position=position + 100000 where position>='3';
insert into page(book,position) values('1','3');
update page set position=position - 99999 where position>100000;
This will work around the unique contraint and seems simpler than dropping it
everytime you want ot insert a page.
-----Original Message-----
From: James K. Lowden <jklow...@schemamania.org>
To: sqlite-users <sqlite-users@sqlite.org>
Sent: Tue, Dec 9, 2014 10:38 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to
fail
On Mon, 08 Dec 2014 22:01:15 +0700
Dan Kennedy <danielk1...@gmail.com> wrote:
> On 12/08/2014 09:55 PM, Nico Williams wrote:
> > Ideally there would be something like DEFERRED foreign key checking
> > for uniqueness constraints...
>
> You could hack SQLite to do enforce unique constraints the same way
> as FKs. When adding an entry to a UNIQUE index b-tree, you check for
> a duplicate. If one exists, increment a counter. Do the opposite when
> removing entries - decrement the counter if there are two or more
> duplicates of the entry you are removing. If your counter is greater
> than zero at commit time, a UNIQUE constraint has failed.
It's not *deferred* constraint checking. It's constraint checking.
Best to honor the transaction first.
Rather than adding to the syntax, perhaps a pragma could cause updates
to happen in a transaction:
1. Create a temporary table to hold the after-image of the updated
rows.
2. begin transaction
3 . Delete the rows from the target table.
3. Insert the updated rows from the temporary table.
4. commit
5. drop temporary table.
Of course there are more efficient answers available deeper in the
update logic, affecting only the partcular columns at the time the
constraint is enforced. I guess they all involve deleting the
prior set from the index and inserting the new one.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
