Friday, January 31, 2020, 9:39:07 PM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 31 Jan 2020, at 9:27pm, Keith Medcalf <kmedc...@dessus.com> wrote: >> You are however correct that this is an "application consistency" >> problem more than an SQLite problem and it is a lot of change for >> little actual benefit. > How about this ? > A program (possibly part of the SQLite precompiled suite, possibly > from a third party) goes through any SQLite database as an integrity > checker. One of its jobs is to check that column constraints > are not violated. This cannot possible be wrong. A SQLite database > with violated CHECK clauses is, by definition, corrupt. > However, because the checks are performed on the values input, not > the values stored, some data in the database does violate a CHECK > constraint. > Can this happen, given the behaviour Keith identified ? If so, I > would say that something is wrong. Probably not, at least not with the example he used. In Keith's example (assuming I understand it correctly), you are stopped from inserting/updating something that -- if you did the insertion/update WITHOUT the checks -- would be stored in such a way that it would later pass "pragma integrity_check" if the checks were in place. So, inserting '1' (a string) into a field with integer affinity, but no constraints would be allowed, and result in 1 (an integer) being stored. If that column were to magically gain a "check (typeof(x) == 'integer')" constraint, it would pass "pragma integrity_check" (because by now it contains an integer, not the string that was originally inserted). If you now repeated the original insert (of the string '1') with the check constraint in place it will now REJECT the insertion, because the type-of-the-thing-being-inserted doesn't meet the constraint (it's a string). So it doesn't allow you to create an inconsistent database (as defined by check constraints), but does stop some ways of inserting/modifying data that would have created valid data if the checks weren't there. Graham _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
