Check constraint problem

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Check constraint problem

Michael Schmidt
New to PostgreSQL and hoping for some help with a constraint I've been struggling with for a could of days.  The table includes means and standard deviations.  They should either both be null or (mean any value and standard deviation >= 0)
 
The constraint statement:
 
ALTER TABLE "ClinData" ADD CONSTRAINT "Control_Score" CHECK
((("Control_Score_M" IS NULL) AND ("Control_Score_SD" IS NULL) ) OR
(("Control_Score_M" IS NOT NULL) AND ("Control_Score_SD" >= 0.0)))
 
This statement executes okay.  It prevents Control_Score_M of NULL and Control_Score_SD = 1.0 (as it should).  However, it allows Control_Score_M = 1 and Control_Score_SD of NULL (it shouldn't).  Any thoughts about what is wrong.  Thanks!
 
Michael Schmidt 
Reply | Threaded
Open this post in threaded view
|

Re: Check constraint problem

Tom Lane-2
"Michael Schmidt" <[hidden email]> writes:
> ALTER TABLE "ClinData" ADD CONSTRAINT "Control_Score" CHECK
> ((("Control_Score_M" IS NULL) AND ("Control_Score_SD" IS NULL) ) OR
> (("Control_Score_M" IS NOT NULL) AND ("Control_Score_SD" >= 0.0)))

> This statement executes okay.  It prevents Control_Score_M of NULL and
> Control_Score_SD = 1.0 (as it should).  However, it allows
> Control_Score_M = 1 and Control_Score_SD of NULL (it shouldn't).  Any
> thoughts about what is wrong.  Thanks!

The check constraint evaluates to NULL, which per SQL spec is not a
failure condition (this is different from the behavior of NULL in WHERE).
You need to add an explicit "Control_Score_SD IS NOT NULL" to the second
part of the constraint.  As is, for values of 1 and NULL you get

        (false AND true) OR (true AND null)
ie
        false OR null
ie
        null

(remember null effectively means "unknown" in SQL's 3-state boolean
logic)

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Check constraint problem

Michael Glaesemann
In reply to this post by Michael Schmidt

On Jul 1, 2005, at 12:04 PM, Michael Schmidt wrote:

> The constraint statement:
>
> ALTER TABLE "ClinData" ADD CONSTRAINT "Control_Score" CHECK
> ((("Control_Score_M" IS NULL) AND ("Control_Score_SD" IS NULL) ) OR
> (("Control_Score_M" IS NOT NULL) AND ("Control_Score_SD" >= 0.0)))
>
> This statement executes okay.  It prevents Control_Score_M of NULL  
> and Control_Score_SD = 1.0 (as it should).  However, it allows  
> Control_Score_M = 1 and Control_Score_SD of NULL (it shouldn't).  
> Any thoughts about what is wrong.  Thanks!

I think the problem may be that Control_Score_SD >= 0.0 is evaluated  
in interesting ways when Control_Score_SD is NULL. What happens if  
you do this?

ALTER TABLE "ClinData" ADD CONSTRAINT "Control_Score"
     CHECK (
         ( ("Control_Score_M" IS NULL)
             AND ("Control_Score_SD" IS NULL) )
         OR ( ("Control_Score_M" IS NOT NULL)
             AND ("Control_Score_SD" IS NOT NULL)
             AND ("Control_Score_SD" >= 0.0) )
     );

You can probably drop the innermost parens, I believe. Might improve  
legibility

ALTER TABLE "ClinData" ADD CONSTRAINT "Control_Score"
     CHECK (
         ( "Control_Score_M" IS NULL
             AND "Control_Score_SD" IS NULL )
         OR ( "Control_Score_M" IS NOT NULL
             AND "Control_Score_SD" IS NOT NULL
             AND "Control_Score_SD" >= 0.0 )
     );

Does this help?

Michael Glaesemann
grzm myrealbox com


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Check Constraint problem

Michael Schmidt
In reply to this post by Michael Schmidt
The solutions provided by Mr. Glaeserman and Mr. Lane worked perfectly.  So that's how NULL works! 
 
Thanks so much for the help.