Re: replacing mysql enum

On Sat, 11 Dec 2004, Ian Barwick wrote:

> (Oddly enough, putting the NULL in the CHECK constraint seems
> to make the constraint worthless:
> test=> create table consttest (field varchar(2)  check (field in
> (null, 'a','b','c')));
> CREATE TABLE
> test=> insert into consttest values ('xx');
> INSERT 408080 1
> test=> SELECT * from consttest ;
>  field
> -------
>  xx
> (1 row)
>
> Not sure what logic is driving this).

The way NULL is handled in IN (because it's effectively an equality
comparison). Unless I miss-remember the behavior, foo in (NULL, ...) can
never return false and constraints are satisified unless the search
condition returns false for some row.  I think this means you need the
more verbose (field is null or field in ('a','b','c'))