On 06/03/2013 10:30 AM, Dominique Devienne wrote:
On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca>
wrote:
Off topic, I'd love a way to request strong typing for a column (so that
attempts to store 'abc' into an int column would fail). You can emulate it
with a pair of before/update triggers (select raise(...) where
typeof(intcol)!='integer'), but that's clunky. Oh well... in retrospect,
most of the times I've been bitten by type mismatches were probably either
due to this bug or (more likely) due to my not specifying any affinity at
all and then being surprised when 1 != '1'.
You don't have to use triggers, you can use a check constraint instead
(simpler, but also perhaps faster as well?).
If you do, you loose some of the implicit type conversions SQLite does,
based on type affinity, so the "1" no longer gets converted to 1.
... which is why I prefer triggers. They kick in after the column's
numeric affinity has a chance to convert "1" to 1.
Gives the best of both worlds, once you get past the boilerplate:
sqlite> create table t(x number);
sqlite> create trigger t1 before insert on t begin
...> select raise(FAIL, 'Numbers only!') where typeof(new.x) =='text';
...> end;
sqlite> insert into t values(1);
sqlite> insert into t values('1');
sqlite> insert into t values('a');
Error: Numbers only!
sqlite> select x,typeof(x) from t;
1|integer
1|integer
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
