From: | PFC <lists(at)boutiquenumerique(dot)com> |
---|---|
To: | "j(dot)random(dot)programmer" <javadesigner(at)yahoo(dot)com>, "Richard Huxton" <dev(at)archonet(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL design question: null vs. boolean values |
Date: | 2005-01-16 18:47:33 |
Message-ID: | opskpk5jr6th1vuj@musicbox |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> create table xyz
> (
> field_foo char(1) check (field_foo in 'y', 'n'),
> foo_detail varchar(255),
> check (
> case
> when field_foo='y' and foo_detail is null
> then false
> else true
> end
> )
> );
A simpler check would be :
CHECK(
(field_foo = 'y' AND foo_detail IS NOT NULL)
OR ( (field_foo = 'n' OR field_foo IS NULL) AND foo_detail IS NULL)
)
Which means " field_foo can be y, n, or NULL, and foo_detail should be
null except if field_foo is 'y' "
Also, IMHO, the Y/N/unknown case should have three values, NULL meaning
'the user has not answered this question'. Because if you insert a blank
row in the table and fill it afterwards, you'll know if it was 'answered
unknown' or 'unanswered'.
From | Date | Subject | |
---|---|---|---|
Next Message | Theo Galanakis | 2005-01-16 23:07:05 | Re: pgmirror |
Previous Message | j.random.programmer | 2005-01-16 13:59:36 | Re: SQL design question: null vs. boolean values |