Dan Bishop wrote:
> If I write
>
> sqlite> CREATE TABLE T1 (N INTEGER CHECK(N >= 0));
>
> the constraint is applied AFTER converting N to an integer.
>
> sqlite> INSERT INTO T1 VALUES('42');
> sqlite> INSERT INTO T1 VALUES('-5');
> SQL error: constraint failed
A curious thing seems to happen. Inside CHECK constraint, a unique situation is
created that probably doesn't exist anywhere else (well, maybe within a
trigger; I'm too lazy to try and repro) - expression N has a value of type TEXT
(convertible to integer) but an INTEGER affinity. The comparison then appears
to coerce both operans to numbers first.
This example allows a negative value to slip past the check:
CREATE TABLE T1 (N INTEGER CHECK(+N >= 0));
INSERT INTO T1 VALUES('-5');
select N, typeof(N) from T1
-5 | integer
The unary plus suppresses type coercion, so '-5' is compared with 0, and any
string is considered greater than any number so the check succeeds. Then the
value is coerced to integer before storage. This proves that CHECK expression
is evaluated before converting the value for storage in all cases; it just so
happens that, in your example, the same conversion is performed when evaluating
the expression itself.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
