Hi, A FOREIGN KEY violation that occurs in IMMEDIATE mode, though it causes an error, is often not actually a real error. Sometimes the "VIOLATION" (of referential integrity) is corrected at a later point ( order of operations is important). Assuming the VIOLATION is corrected , then in DEFERRED mode at the time of COMMIT (when the FOREIGN KEY constraints are checked ) no error will be flagged as the DB is referential valid.
But I am trying to understand the reason for the 1000x performance hit. SQlite continues to run, but seems to be in some perpetual state of confusion after the 'silent' FOREIGN KEY violations occur in DEFERRED mode. http://cs.stanford.edu/people/widom/cs145/sqlite/SQLiteRefIntegrity.html refers to something similar "Bulk-Loading <http://cs.stanford.edu/people/widom/cs145/sqlite/SQLiteLoad.html> into a SQLite database while checking referential integrity is very, very slow" Note sure if that is related. On Thu, Oct 1, 2015 at 9:36 AM, Klaas V <klaasvanbe at yahoo.com> wrote: > Andrew Cunningham wrote: > >I have noticed that when FOREIGN KEY is in DEFERRED mode and a FOREIGN KEY > >violation occurs, SQLite will continue to run, but performance slows down > >by about 1000x. Breaking into the code shows that it seems to be > >continually re-reading the database. When I finally get to COMMIT , an > > When any violation occurs it's wise to use '.bail on' (ref. > https://www.sqlite.org/cli.html ) > and eventually abort the application (creating a core dump if necessary to > get an investigation done by a developer) as soon as it stops to run for > this reason. > > Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Gr?sse, > Klaas `Z4us` V - LinkedIn 437429414 >
