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

Reply via email to