To: "j.random.programmer" <javadesigner@yahoo.com>,
	"Richard Huxton" <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL design question: null vs. boolean values
References: <20050116135936.28081.qmail@web14206.mail.yahoo.com>
Message-ID: <opskpk5jr6th1vuj@musicbox>
Date: Sun, 16 Jan 2005 19:47:33 +0100
From: PFC <lists@boutiquenumerique.com>
Organization: =?iso-8859-15?Q?La_Boutique_Num=E9rique?=
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
In-Reply-To: <20050116135936.28081.qmail@web14206.mail.yahoo.com>
User-Agent: Opera M2/7.54 (Linux, build 751)


> 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'.