On 10 Mar 2018, at 7:15am, John Found <johnfo...@asm32.info> wrote:
> Simon Slavin <slav...@bigfraud.org> wrote:
>
>> On 9 Mar 2018, at 7:49pm, John Found <johnfo...@asm32.info> wrote:
>>
>>> In the current implementation "insert or replace" behave as the foreign
>>> constraint is deferred.
>>> But according to documentation, all foreign constraints in SQLite are
>>> immediate by default.
>>
>> create table B (
>> aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED,
>> ulti_data)
>
> No difference at all. Because "insert or replace" always works as if the
> constraint is deferred.
> "insert or replace" always succeed to delete rows that are referenced by B
> and defers the constraint enforcement until
> the end of the internal transaction. But at the end, a new row with the same
> ID is inserted, so there is no
> constraint violation anymore.
John, I apologise. I missed a paragraph in the documentation:
"If the current statement is not inside an explicit transaction (a
BEGIN/COMMIT/ROLLBACK block), then an implicit transaction is committed as soon
as the statement has finished executing. In this case deferred constraints
behave the same as immediate constraints."
So please keep the CREATE TABLE statement the same as quoted above, but change
your data commands to
BEGIN;
insert or replace into A values (?1, ?2);
insert into B values (?1, ?2);
COMMIT;
or even
BEGIN;
insert or replace into A values (?1, ?2);
COMMIT;
insert into B values (?1, ?2);
Do these make things work the way you expect ?
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
