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