Re: [sqlite] Is it a bug or "as designed"?

On 10 Mar 2018, at 7:15am, John Found wrote: > Simon Slavin wrote: > >> On 9 Mar 2018, at 7:49pm, John Found wrote: >> >>> In the current implementation "insert or replace" behave as the foreign >>> constraint is deferred.

Re: [sqlite] Is it a bug or "as designed"?

On Sat, 10 Mar 2018 01:17:38 + Simon Slavin wrote: > On 9 Mar 2018, at 7:49pm, John Found wrote: > > > In the current implementation "insert or replace" behave as the foreign > > constraint is deferred. > > But according to documentation, all

Re: [sqlite] Is it a bug or "as designed"?

On 9 Mar 2018, at 7:49pm, John Found 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. John, The documentation

Re: [sqlite] Is it a bug or "as designed"?

On 2018/03/09 9:49 PM, John Found wrote: On Fri, 9 Mar 2018 19:42:19 + Simon Slavin wrote: You are right. And Jay Kreibich in his post above. But then the second solution from my post should be the correct behavior. In the current implementation "insert or replace"

Re: [sqlite] Is it a bug or "as designed"?

> On Mar 9, 2018, at 1:42 PM, Simon Slavin wrote: > > "replace" means "delete the original row, then insert a new one”. More properly, it means “delete any and all rows that might cause any conflict with inserting the new row.” There really isn’t a concept of an

Re: [sqlite] Is it a bug or "as designed"?

On Fri, 9 Mar 2018 19:42:19 + Simon Slavin wrote: > On 9 Mar 2018, at 7:11pm, John Found wrote: > > > "insert or replace" succeed without deleting the old rows from B. > > "replace" means "delete the original row, then insert a new one". > > In

Re: [sqlite] Is it a bug or "as designed"?

On 9 Mar 2018, at 7:11pm, John Found wrote: > "insert or replace" succeed without deleting the old rows from B. "replace" means "delete the original row, then insert a new one". In your code, figure out whether you need INSERT or UPDATE, and do the appropriate one.

Re: [sqlite] Is it a bug or "as designed"?

Foreign keys enforcement can get tricky depending on the enforcement policy, transactions, and a lot of things. I don’t have enough experience to comment on that fully. I will say this, however, because it is a common mistake with a lot of different aspects of database behavior:

[sqlite] Is it a bug or "as designed"?

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,