Re: Check constraint problem

"Michael Schmidt" <MichaelMSchmidt@msn.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