Re: replacing mysql enum

Ian Barwick <barwick@gmail.com> writes:
> (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

For a non-null field value, that IN clause reduces tonull OR false OR false OR false
which reduces to null because of the way 3-state boolean logic is
defined in SQL (which makes sense if you interpret null as "unknown").
And a null result from CHECK is defined not to be a failure case by
the SQL standard.

This is really the same logic that allows the explicit-null-free CHECK
condition to accept NULLs:null IN ('a','b','c')
becomesnull OR null OR null
becomesnull
which doesn't fail.

I believe this was intentional on the part of the SQL committee.  Their
thought was that if you intend to disallow NULLs, you should write an
explicit NOT NULL constraint, separately from any CHECK you might write.
Therefore, defining CHECK such that it tend to fall through silently on
NULL inputs is a good thing.
        regards, tom lane