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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
