OK, I already had removed my constraint as it is, since it is obviously invalid
So for me : Am i right to leave a constraint, say CHECK (varname=0 OR varname=1), to avoid other integer values, or is the constraint useless? (i guess the answer will be 'it depends on your code' :-P) And for sqlite itself : As stated in the second question of my first message, shouldn't there be some check in sqlite that the type in the constraints are compatible with the fields, with an error at table creation? 2015-08-17 17:56 GMT+02:00 Simon Slavin <slavins at bigfraud.org>: > > On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <barthelemy at geovariances.com> > wrote: > > >> SQLite has no such type > > > > I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything > else. > > The statement is passed as is to sqlite. > > SQLite is interpreting BOOLEAN as NUMERIC. See the last line of the table > in section 2.2 of > > <https://www.sqlite.org/datatype3.html> > > This is in most cases the right thing to do, since BOOLEAN values stored > by most SQL programming are really the numbers 0 and 1. However your CHECK > constraints treat the values as if they're strings: > > > storage_implicit = 'true' OR > > storage_implicit = 'false' > > and if the values are understood as NUMERIC, neither of those string > comparisons will ever by true, so all INSERT commands will fail their > constraint checks. > > > On previously created sqlite files, when i open then in sqlite manager > > addon of Firefox, the field is still displayed as BOOLEAN > > FireFox is showing you the command that was used to create the table. > This is an (unfortunate ?) aspect of how SQLite works: it stores the > creation string rather than details of how it was understood. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > 2015-08-17 17:56 GMT+02:00 Simon Slavin <slavins at bigfraud.org>: > > On 17 Aug 2015, at 4:50pm, Olivier Barthelemy <barthelemy at geovariances.com> > wrote: > > >> SQLite has no such type > > > > I see nothing in Qt code that converts BOOLEAN in NUMERIC or anything > else. > > The statement is passed as is to sqlite. > > SQLite is interpreting BOOLEAN as NUMERIC. See the last line of the table > in section 2.2 of > > <https://www.sqlite.org/datatype3.html> > > This is in most cases the right thing to do, since BOOLEAN values stored > by most SQL programming are really the numbers 0 and 1. However your CHECK > constraints treat the values as if they're strings: > > > storage_implicit = 'true' OR > > storage_implicit = 'false' > > and if the values are understood as NUMERIC, neither of those string > comparisons will ever by true, so all INSERT commands will fail their > constraint checks. > > > On previously created sqlite files, when i open then in sqlite manager > > addon of Firefox, the field is still displayed as BOOLEAN > > FireFox is showing you the command that was used to create the table. > This is an (unfortunate ?) aspect of how SQLite works: it stores the > creation string rather than details of how it was understood. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
