[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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

From: Karsten Hilbert
Subject: Re: [Gnumed-devel] postgres boolean checks, and (e.g) diagnoses
Date: Sun, 19 Sep 2004 13:21:24 +0200
User-agent: Mutt/

> >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?

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

> - 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

BTW, there is another constraint on that table:

 if active then significant
 if inactive then either significant or not

And there is potentially one more:

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

What do you think ?

GPG key ID E4071346 @
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

reply via email to

[Prev in Thread] Current Thread [Next in Thread]