On 4/7/11 2:52 PM, "Jay A. Kreibich" <[email protected]> wrote:
>On Thu, Apr 07, 2011 at 02:44:49PM -0700, Duquette, William H (318K) >scratched on the wall: >> Howdy! >> >> I have a database with tables defined like this: >> >> CREATE TABLE table1 ( >> id INTEGER PRIMARY KEY, >> value REAL DEFAULT 1.0 CHECK (0.0 <= value) >> ); >> >> CREATE TABLE table2 ( >> id INTEGER PRIMARY KEY, >> value REAL DEFAULT 1.0 CHECK (0.0 <= value AND value <= 1.0) >> ); >> >> The following statements should each fail with a constraint error: >> >> UPDATE table1 SET value='NONESUCH' WHERE id=1; >> UPDATE table2 SET value='NONESUCH' WHERE id=1; >> >> >> For some reason, setting the value in table2 fails, as it should, but >> setting the value in table1 succeeds when it shouldn't. > > Text values are greater than numeric values. Hence the check is > valid: http://sqlite.org/datatype3.html#comparisons > > > You might want to add CHECK ( typeof( value ) == 'real' ) Hmmm. I tried this; but this constraint fails for ANY value I give it. I'm using the Tcl interface to SQLite3; a numeric value in Tcl can be passed to SQLite3 as a number or as a string, depending on how it has been used in the Tcl. When it's passed as a string, SQLite3 will still use it as a number in expressions, but apparently it isn't converting it to an actual number before the constraint is checked. I tried this: CHECK (0.0 <= CAST (value AS real)) But this just casts the string 'NONESUCH' to 0.0, which is no help. I tried this: CHECK (0.0 <= (value + 0.0)) hoping that that would fail if value was really non-numeric, but succeed otherwise Nope; no help. Is there a way that I can write a constraint that accepts both real numbers and numeric strings, and does the range checking, but rejects non-numeric strings? I can do the type-checking in Tcl if I have to, but I'd rather not have to. Will > > > -j > >-- >Jay A. Kreibich < J A Y @ K R E I B I.C H > > >"Intelligence is like underwear: it is important that you have it, > but showing it to the wrong people has the tendency to make them > feel uncomfortable." -- Angela Johnson >_______________________________________________ >sqlite-users mailing list >[email protected] >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
