The way I <think> it happens with deferred foreign keys is this (Gut feeling from observations. Experts please correct me if I'm way off):
Keep track with an integer, let's call it "netBroken" While netBroken = 0 then when you insert a row you don't have to check if anything references it, only if it references something. (In this case that's a quick search with the primary key) If it does, then you're good. If it doesn't, then increment netBroken to let you know "hey, I broke something" If netBroken is != 0, then when you insert a row you not only have to check if the new row is broken, but you also have to check if it fixes a previously broken thing. (In this case that includes an unindexed search on aid) If the new row doesn't reference something, then increment netBroken. If something references the new row, then decrement netBroken by the number of rows that reference it, "hey, I fixed something" At the end of the transaction look at what you have for netBroken. If it's 0 then yay! Your balance sheet came out ok and you can commit. If it's non-zero then you didn't fix everything you broke. This also explains why with deferred foreign keys it can't tell you what record violated things, because it didn't keep track of the individual record, only the net count of broken things. So for your test script there, try inserting a record with the violation ID a little bit later and see if it suddenly speeds up again. In my Python version of your script it does indeed speed back up again once the outstanding violation is fixed. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, August 02, 2018 4:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Violated failed foreign key constraint delays the rest of transaction ; Some foreign key violations don't trigger the error at all On Thu, Aug 2, 2018 at 10:34 AM Keith Medcalf <kmedc...@dessus.com> wrote: > > Yes. Look at the CREATE TABLE for table A (completely ignore table B as > it serves no purpose whatsoever).... > Rah, silly me... I assumed A.aid referenced B.id. Why have a B table at all then. > Also, if you create an index on the child key as is required for > performance, there is no "slowdown" whatsoever.... Of course. Something I always do in Oracle, index all my FK columns... Silly me again. Boy, not my morning :). Thanks for setting me straight Keith. --DD _______________________________________________ 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
