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
