Re: Check constrain execution timing change?

CREATE TABLE t1(x INTEGER CHECK(typeof(x)=='text'));
INSERT INTO t1 VALUES('123');
PRAGMA integrity_check;


On 1/31/20, Graham Holden <[email protected]> wrote:
> Friday, January 31, 2020, 9:39:07 PM, Simon Slavin <[email protected]>
> wrote:
>
>> On 31 Jan 2020, at 9:27pm, Keith Medcalf <[email protected]> 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
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users