I have two tables with foreign constraint:
create table A ( id primary key not null, single_data );
create table B ( aid references A(id) on delete cascade, multi_data);
Now I am periodically inserting data in A and B with the following queries:
insert or replace into A values (?1, ?2);
insert into B values (?1, ?2);
Unfortunately, after replacing some row in A, all previously inserted rows in B
got deleted, even if the value of ID does not changes. Here SQLite works
exactly as it first deletes the conflicting row from A and then inserting new.
Now, if I define the table B without "on delete":
create table B ( aid references A(id), multi_data);
"insert or replace" succeed without deleting the old rows from B.
In my opinion this behaviour is not consistent. The consistent solutions IMHO
are two:
1. "insert or replace" succeed both in the first and in the second case without
deleting rows from B,
2. "insert or delete" succeed in the first case, deleting all constrained rows
from B and fails in the second case with "FOREIGN KEY constraint failed".
The first case IMHO is more intuitive and natural. At least this was my
expectation when writing the code.
--
http://fresh.flatassembler.net
http://asm32.info
John Found <[email protected]>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
