On Friday, 31 January, 2020 13:58, Richard Hipp <d...@sqlite.org> wrote:
>On 1/31/20, Keith Medcalf <kmedc...@dessus.com> wrote: >> The check should occur AFTER defaults and >> column affinity is applied before the data record is stored >Why do you think this? Is it documented somewhere? I ask because >your test case gives the same answer (doing the first insert but >failing the other two) for every version of SQLite I checked from >trunk going back to 3.1.0 (2005-01-21). Hence, for backwards >compatibility, even it is documented to do something differently, I >should probably change the documentation rather than the behavior. You are absolutely correct in that this is the way SQLite has worked forever, and is the way it is documented (or not, I am not sure). However, this means that the result of a CHECK/NOT NULL contstraint differs between an INSERT and when run on existing data (via pragma integrity_check). This means that, for example, the construct CHECK (typeof(x) in ('integer', 'real')) enforces different table invariants between UPDATE/INSERT and when the data is already in the table since in the case of INSERT/UPDATE it checks the type of the value input to the UPDATE/INSERT rather than the value actually stored and prohibits the INSERT/UPDATE of values that would pass the table checks later. 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. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users