Re: Check constraint problem

"Michael Schmidt" <MichaelMSchmidt(at)msn(dot)com> 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