Обсуждение: Check constraint problem

Поиск
Список
Период
Сортировка

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 

Re: Check constraint problem

От
Michael Glaesemann
Дата:
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

Re: Check constraint problem

От
Tom Lane
Дата:
"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