Hey all, SQLite seems to be consistent if you actually provide the proper checks you want to achieve. What's the deal again with prepending the check with "+"?
sqlite> CREATE TABLE T2 (N INTEGER CHECK(N >= 0) CHECK(TYPEOF(N)=='integer')); sqlite> INSERT INTO T2 VALUES(-'q'); sqlite> INSERT INTO T2 VALUES('q'); Error: constraint failed sqlite> INSERT INTO T2 VALUES('-q'); Error: constraint failed sqlite> INSERT INTO T2 VALUES('-2'); Error: constraint failed sqlite> INSERT INTO T2 VALUES('2'); Error: constraint failed sqlite> INSERT INTO T2 VALUES(-'2'); Error: constraint failed sqlite> INSERT INTO T2 VALUES(-2); Error: constraint failed sqlite> INSERT INTO T2 VALUES(2); sqlite> SELECT N, TYPEOF(N) FROM T2; 0|integer 2|integer #Suppress type coercion sqlite> CREATE TABLE T3 (N INTEGER CHECK(+N >= 0) CHECK(TYPEOF(N)=='integer')); sqlite> INSERT INTO T3 VALUES('q'); Error: constraint failed sqlite> INSERT INTO T3 VALUES('-q'); Error: constraint failed sqlite> INSERT INTO T3 VALUES(-'q'); sqlite> INSERT INTO T3 VALUES(-'2'); Error: constraint failed sqlite> INSERT INTO T3 VALUES(-'iii'); sqlite> INSERT INTO T3 VALUES(-2); Error: constraint failed sqlite> INSERT INTO T3 VALUES('-2'); Error: constraint failed sqlite> INSERT INTO T3 VALUES('2'); Error: constraint failed sqlite> SELECT N, TYPEOF(N) FROM T3; 0|integer 0|integer On Thu, Apr 29, 2010 at 5:35 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > Alexey Pechnikov wrote: > > It's bad solution: > > > > sqlite> CREATE TABLE T1 (N INTEGER CHECK(+N >= 0)); > > sqlite> INSERT INTO T1 VALUES('-5'); > > sqlite> INSERT INTO T1 VALUES('q'); > > sqlite> select N, typeof(N) from T1; > > -5|integer > > q|text > > I wasn't proposing this as a solution. It was an experiment, an attempt to > illustrate what was going on. I though I made it clear - I even show that > negative values slip through this check ('q' would pass the original check, > too). I can only assume you haven't actually read my message before jumping > in to respond. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Paul Rigor Pre-doctoral BIT Fellow and Graduate Student Institute for Genomics and Bioinformatics Donald Bren School of Information and Computer Sciences University of California, Irvine http://www.ics.uci.edu/~prigor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users