Re: [Gnumed-devel] postgres boolean checks, and (e.g) diagnoses

[Karsten Hilbert]

> >clin_diag_is_active CHECK (((is_chronic = false) OR ((is_chronic = 
> >true) AND (is_active = true))))
> 
> Does a "check" function as a validity test that is applied only when 
> the boolean is attempted to be set as "true" or does it test the 
> validity of both true and false values?
Both.

> In this example
> - if the diagnosis is *not* chronic, then is_active is permitted 
> to be either true or false?
Yes.

> - in order to be is_active, the diagnosis must also be chronic?
Not quite. If not chronic then it can be either active or not.
If chronic, however, it must be active. But don't be fooled,
the constraint is wrong, good that you asked :-)   See below.

> This is confusing because the diagnosis defaults to is_active
> but NOT is_chronic,
Sometimes it is very useful to put such conflicting
constraints on columns, say

create table t (
    must_set text
        not null
        default null
);

This would force the programmer to explicitely set the value
or inserts will fail.

Now, the above constraint is wrong, since a disease can sure
be chronic but not active, eg. in a dormant phase. Just think
of Mb. Crohn or Multiple Sclerosis. I'll remove the
constraint.

BTW, there is another constraint on that table:

 if active then significant
    or
 if inactive then either significant or not

And there is potentially one more:

 if chronic then significant
    or
 if not chronic then either significant or not

What do you think ?

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346