Check constraint problem
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
"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
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