CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
INSERT INTO t1 VALUES('123');
PRAGMA integrity_check;On 1/31/20, Graham Holden <sql...@aldurslair.com> wrote: > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
