On Tuesday, 17 November, 2015 20:29, Richard Hipp said: > On 11/17/15, Yuri <yuri at rawbw.com> wrote:
> > Don't keep the bag, keep only one integer ID of the first failed > > constraint. > Therein lays the rub: there is no way to tell which (if any) FK > constraint has failed until you have run the operation to the end and > checked them all. Remember, an FK constraint is allowed to be > violated during a transaction, as long as the violation is resolved > before the end of the transaction. Richard, I don't think that is entirely true. When a constraint is set to IMMEDIATE (the default) then each interim operation (statement) must leave the database in a consistent state at the end of the statement, even within a transaction. Only a DEFERRABLE INITIALLY DEFERRED constraint can leave the database in an inconsistent state between operations (statements) until commit time (or if pragma defer_foreign_keys=on which turns all foreign key constraints into deferred constraints). In the case of deferred constraints producing an actionable constraint violation message that is meaningful would be difficult. Even if one were to produce a message that constraint x was violated, you would have to know which one of the myriad of transformations (statements) in the transaction led to the condition. This would probably require the use of an embedded database engine of some type to track. > With CHECK, and UNIQUE, and NOT NULL constraints, you do know that the > constraint has failed right away. And for those constraints, SQLite > does provide more detail about exactly which constraint failed. But > for FK constraints, you never know if a constraint that is failing > right now might be resolved before the end of the transaction. However, for immediate constraints which must be satisfied at the end of each statement within a transaction (before commit), would it not be possible to pop out which constraint failed since it must (obviously) be the one that is being checked at the moment, for that exact statement, much the same as for CHECK, UNIQUE and NOT NULL, which also must be satisfied for each statement? If there is an unsatisfied deferred constraint at commit time, then the designer of the database schema ought to be able to analyze the issue since this is why they chose to use deferred constraints in the first place. That they are using deferred constraints ineffectually is an error in programmer analysis, design, and understanding. While it is the job of the database to prevent such inconsistent states from being committed to the database, doing "heavy" analysis of what is (obviously) inherently ill-conceived design of the schema and the transaction processing is not within the scope of a "lite" database. However, indicating an immediate constraint violation with attribution for a non-deferred constraint is much smaller problem that can only exist within the VDBE code execution for the single statement. Perhaps something like an attributed immediate constraint violation message for immediate constraints, but for constraint violation in deferred constraints a simple "deferred constraint violated" should be sufficient (although it should be obvious that a constraint violation detected at COMMIT time must only be a deferred constraint and cannot arise from an immediate constraint). A little off topic, this reminds me of a feature of the PL/1 F compiler. The PL/1 compiler was a huge monstrous beast that had both syntactical and semantic analyzer paths so it could "correct" a number of common programming errors, faulty assumptions, and misspelling of keywords if the programmer were too lazy to formulate the problem and the source code correctly. Because code was painstaking (slowly) entered onto decks of 80-column hollerith punch cards that were submitted for execution overnight (or, in many cases over-fortnight), this "feature" made it possible for many "sloppy" errors to be automatically corrected thus avoiding the fortnightly correct-submit-result-edit-repeat process. For example, in "C" you may get an error that some variable is not declared and the compilation halts. In PL/1, the compiler assumes that you are simply a forgetful and sloppy programmer and "creates" the necessary declaration for you. This allows the compilation and execution to continue. If the assumption the compiler made turns out to be correct, this saved you two weeks of time. If the compiler was in error, it cost you nothing -- you still have to correct the program source and do another fortnight turn-around. Of course, the compiler also had an option to "punch" a new card deck of the "corrected" and "properly formatted" source. The long and the short of this is that it encouraged programmers to write sloppy ill-formatted and error-filled code and let the compiler fix it up. If it did so correctly, then you ended up with a working program in a much shorter period of time and a beautifully formatted deck of source that was completely free of compiler detectable syntactical or semantic errors. True expert programmers knew exactly how the assumptions that the compiler made worked, and instead of wasting time punching the "correct" code, punched code in a hugely shorter format in the firm knowledge that the compiler would fix it exactly correctly saving much cardboard and much time. The not so skilled often could not tell whether the compiler was "doing the right thing" or not. To bring this up to modern times, programming now has a cycle time of minutes rather than fortnights. It is now typical to expect the compiler or the database to tell you what your syntactical and semantic errors are, and to only fix things that are complained about. So back to topic -- the wish to have SQLite be a "heavy" system in the ilk of PL/1 is just a compensation for inadequate wattage being deployed in the first instance during the problem analysis stage -- the failure of a deferred constraint is a programmer/system analysis error (a wetware error). Where the error is should be obvious since it was the analysis stage which led to the constraints being deferred in the first place. The ultimate "lite" system, in my opinion, was the ancient TRS-80 BASIC. It had only three error messages from which you ought to be able to determine types of errors they represent: What? How? Sorry?
