On 07/03/2013 1:07 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 11:44 AM, Ryan Johnson
<ryan.john...@cs.utoronto.ca> wrote:
On 07/03/2013 12:27 PM, Nico Williams wrote:
On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson
<ryan.john...@cs.utoronto.ca> wrote:
I would argue that, if a column has type affinity, CHECK should work with
the value that would actually get stored, not the one that was assigned.
But then you couldn't check the value that was attempted to store.
You'd be stuck with dynamic type conversions in all cases.
Is that a bad thing? Forbidding dynamic type conversions to occur at all is
very different than requiring that they always succeed if/when they do
occur, and overly strict IMO.
I prefer strong static typing and no automatic type conversions, but
the way SQLite3 is now I can get all of:
- duck typing (no constraints)
- strong typing with automatic type conversions (see earlier posts in
this thread)
- strong typing with no automatic type conversions (ditto)
That's fairly flexible. There's something to be said for that.
True, but I'd happily give up static strong typing rather than deal with
the current mess. YMMV.
Besides, the check is defined to verify the column, not on the value that
came from the user. In theory you should be able to defer all checks until
the end of a transaction (for all the same reasons deferred FK checking can
be important), and that would cause a behavior change as things currently
stand (the attempted-to-store value would be long gone by then).
You might defer checks, but not type conversions. In any case, I see
no value in deferring check constraints.
Anything constraining cardinality. The old example of "there must always
be three doctors on duty in the ER" comes to mind: if you check() for an
exact count, it becomes very hard to make changes to the schedule
without deferred checking.
Anyways, think of CHECK constraints as equivalent to BEFORE
INSERT/UPDATE triggers.
They're not equivalent. The before trigger sees only the value that was
actually stored, because it runs as a separate program after the
insert/update completes.
WAT?
Yup. I've had several emails in this thread with examples. Here's the
short version:
sqlite> create table t1(x);
sqlite> create table t2(x integer);
sqlite> create table t3(x integer check(typeof(x)='integer'));
sqlite> create table t4(x integer);
sqlite> create trigger t4t before insert on t4 begin select raise(FAIL,
'ints only!') where typeof(new.x)!='integer'; end;
sqlite> insert into t1 values(3);
sqlite> insert into t1 values(3.0);
sqlite> insert into t1 values(30e-1);
sqlite> insert into t1 values('3');
sqlite> insert into t1 values('3.0');
sqlite> insert into t1 values('30e-1');
sqlite> insert into t2 select * from t1;
sqlite> insert into t3 select * from t1;
Error: constraint failed
sqlite> insert into t4 select * from t1;
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
