Are we confusing immediate constraints (checked per statement) with DEFERRED constraints (checked at COMMIT time) again?
> -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of R Smith > Sent: Tuesday, 5 April, 2016 06:58 > To: sqlite-users at mailinglists.sqlite.org > Subject: Re: [sqlite] FOREIGN KEY constraint failed > > > > On 2016/04/04 10:43 PM, Domingo Alvarez Duarte wrote: > > Thanks for reply ! > > > > I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE" > and > > when set throw any kind of error to stderr/sqlite3_(hook) this way ther > is no > > need to store temporary conditions to show later. > > > > And of course sqlite knows which table/field failed to flag the error, > it > > doesn't throw a dice to do it. > > The thing you are missing, is that there might be thousands of FK > violations throughout a transaction, all of which (or most of which) > might get resolved before the end of the transaction, and as such is > absolutely useless to inspect/record/notify/whatever. > > Let's assume there are one thousand violations, and three of them did > not get resolved, such as violation number 322, no. 567 and no. 828. > If you "ask the user" or the program via API about every one of the 1000 > violations, surely the time-waste will be intense, and even if you can > live with the time-waste, how will the application/user ever know that > violation no. 435, for instance, is going to definitely get resolved so > that it might report back to the API some form of "OK, we can accept > this one" or record for its own purposes the violation to "deal with > later" when in fact at some point it gets resolved without necessarily a > second check and certainly not a second failure to revisit it? > > It is extremely rare that the "Last violation" (number 1000 in our > example above) is going to end up being THE ONE, or even "one of" the > offenders. Perhaps only in cases where there is only 1 FK violation in > the entire scope of the transaction, and those cases are rarest (such as > a single insert/delete) and if it does happen, you already know the > exact item causing violation, so the API to disclose its identity is > superfluous. > > The only way this feature is feasible is keeping a complete list of > violations internally and a mechanism to revisit them marking the > resolve (if any). The mechanism itself will be heavy and the memory > footprint of the list can run into gigabytes easily, even for a mediocre > database, seeing as a transaction updating 10 rows may easily need to > check hundreds of FK constraints and recursed constraints. > > As Mr. Bee pointed out - we see this question asked often, lots of > people would like to have it implemented. This may be true, but that's > simply because, without investigation, the concept/implementation seems > easy to lots of people. It only seems that way though. > > Cheers, > Ryan > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
