On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson <[email protected]>
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.
I also would prefer a strong-typing mode though (as an opt-in pragma for
example), rather than adding a bunch of check constraints, and built-in
strong-typing would likely be faster. But Dr. Hipp prefers dynamic typing,
so dynamic typing it is :).
--DD
sqlite> create table t (id number);
sqlite> insert into t values (1);
sqlite> insert into t values ("1");
sqlite> insert into t values ("1x");
sqlite> select id, typeof(id) from t;
1|integer
1|integer
1x|text
sqlite> create table tt (id number check (typeof(id) = 'integer'));
sqlite> insert into tt values (1);
sqlite> insert into tt values ("1");
Error: constraint failed
sqlite> insert into tt values ("1x");
Error: constraint failed
sqlite> select id, typeof(id) from tt;
1|integer
// recent SQLite versions also now report the name of the constraint that
failed, if available.
sqlite> create table ttt (id number, constraint id_is_integer check
(typeof(id) = 'integer'));
sqlite> insert into ttt values (1);
sqlite> insert into ttt values ("1");
Error: constraint id_is_integer failed
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
