Thread: 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
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
"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