Examining the VDBE code generated from some simulated operations explains how it works with stark clarity.
There are two constraint violation counters. One has a "statement" scope (for immediate constraints), and the other a "transaction" scope (for deferred constraints). Basically when the statement is executed, the appropriate violation counter is incremented for each violation -- even if there is a program in place that would "fix" the error (such as an ON ... CASCADE, etc) or even a user-defined trigger. Then when the program/triggers is actually run successfully the appropriate violation counter is decremented. Clearly you have to do it this way because this program could in turn fire a trigger (for example -- since it may update other tables) which itself might end up having an unresolvable constraint violation or otherwise fail to "resolve" the violation. If the value of the immediate violation counter is non-zero at the end of the statement, then a constraint violation error is thrown. There is no way to tell which of the constraints caused the error (it could be a constraint on a table not mentioned in the query that was updated as the result of a ON ... CASCADE or a user-provided trigger or a program or trigger on any of them recursively). For deferred constraints the processing is the same, just using a different counter that persists across statements within a transaction. Statements which "clear" a pre-existing deferred constraint violation, decrement the deferred violation count. At commit, a non-zero deferred violation count throws the constraint violation error. Constraints such as CHECK, NOT NULL and UNIQUE cannot be "fixed" after they happen by a triggered update of any kind within the same statement -- nor can they be deferred -- and therefore they can be reported and attributed as soon as the violation is detected. > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin > Sent: Wednesday, 18 November, 2015 03:44 > To: SQLite mailing list > Subject: Re: [sqlite] How hard is it to add the constraint name to the > 'FOREIGN KEY constraint failed' message? > > > On 18 Nov 2015, at 10:05am, Dominique Devienne <ddevienne at gmail.com> > wrote: > > > Given this pragma, then showing the FK name on immediate > > mode is possible, no? > > Unfortunately not. [following explanation is, I suspect, simplified] > > When the table schema is read from the file the table's constraints are > 'compiled' into a set of rules. A single rule might express the > requirements of many different constraints. Rules are more compact, > faster and simpler to apply than analyzing and applying each constraint > for each value changed. Unfortunately because the structure of the rules > doesn't map 1-to-1 into the constraints they came from it's not possible > to tell, just from 'rule fails', which of the constraints that made up the > rule was broken. > > Applying the original constraints one by one would make SQLite far slower > even for operations which did not violate any constraints, and the > majority of users, who don't violate constraints, would get no benefit > from it. > > An earlier poster came up with a good solution: the existing 'fast check' > is done first and then, if and only if a rule fails, a more complicated > slower check has to be done to figure out which constraint to report as > causing the error. This would complicate SQLite's code, and as before > most users wouldn't benefit from it. > > As mentioned above SQLite4 is written differently and it's possible that > reporting the violated constraint might be 'cheaper' to add to that. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
