Hmmm...when I get rid of the "+'" CREATE TABLE T1 (N INTEGER CHECK(N >= 0)); the constraint works Seems to me that "+N" is the same as "abs(N)". I'm not even sure of what the intent of "+N" would be??? Michael D. Black Senior Scientist Northrop Grumman Mission Systems
________________________________ From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik Sent: Wed 4/28/2010 7:00 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] CHECK constraints and type affinity 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users