Re: Check constraint problem

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