Thanks Peter, that's exactly what I wanted. I've gone with your first (more strict) solution.
Cheers. Date: Sat, 02 Aug 2014 22:42:41 -0700 From: Peter Aronson <pbaron...@att.net> To: sqlite-users@sqlite.org Subject: Re: [sqlite] Prevent non-Integers going into int fields. Message-ID: <53ddcbd1.6060...@att.net> Content-Type: text/plain; charset=ISO-8859-1; format=flowed If you don't mind requiring that the value being inserted be typed integer, you can use: create table t1 (c1 integer check (typeof(c1) = 'integer')); But this means that values that could be converted to integers by the integer affinity of the column, like 4.0 or '1' will be cause a check constraint violation. If this is a problem, a better approach might be to use cast as like this: create table t2 (c1 integer check (cast (c1 as integer) = c1)); Since I *think* cast ought to work pretty much the same a conversion via affinity, producing a consistent set of results. And this has the advantage of round that text integers will still be allowed. Peter On 3 August 2014 16:52, Richard Warburton <rich...@skagerraksoftware.com> wrote: > Whilst any type going into a field is often seen as a big plus, there are > occasions where this may be undesirable. > > For such occasions, I'm currently thinking that: > NOT NULL CHECK( ROUND(fieldname)=fieldname ) > > looks ok, but I'm wondering if there's a case that still gets through or > if there's a better way. > > Thanks. > > -- Richard Warburton - MSc(Hons), PGDipSci, BE(Hons) +64 9 377-2881 ext 9 Senior Developer - http://www.skagerraksoftware.com/ Skagerrak Software - P.O.Box 56-710 / Level 1, 371 Dominion Road, Mt Eden, Auckland 1024 NZ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
