Field Constraints

On 6 Mar 2002 at 11:47, Joseph Maxwell wrote:

Hello,
I have created a DB and TABLE but would like to constrain one field to
accept either "YES" or "NO" only. How can I set this up? I have tried
various syntactic constructs of

[snip]

-- Joe --
PS-------------------------------------------------------------------------
--------------

Just learned that the datatype should be 'bool' in this case

haven't tested it yet

But had I wanted to restrict it to say either mike or john, or even one of
a list say, 'tea', 'cofee', 'chocolate' or 'espresso', how should that be
constructed?

Try:

found_in_ports boolean not null
default 'N'
check (
found_in_ports in ('Y','N'))

The same way as the above AFAIK>
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

CHECK ("Prior_Rx" ~ '[Yy][Ee][Ss]$|[Nn][Oo]$');

http://www.pgexplorer.com
PostgreSQL GUI

----- Original Message -----
From: "Joseph Maxwell" <jemaxwell@jaymax.com>
To: <pgsql-general@postgresql.org>
Sent: Wednesday, March 06, 2002 9:47 PM
Subject: [GENERAL] Field Constraints

Hello,
I have created a DB and TABLE but would like to constrain one field to
accept either "YES" or "NO" only. How can I set this up?
I have tried various syntactic constructs of

Prior_Rx varchar(3) NULL CHECK ('Yes', 'No'),
Prior_Rx varchar(3) NULL CHECK (VALUE 'Yes', 'No'),
Prior_Rx varchar(3) NULL CHECK ('Yes', 'No'),
Prior_Rx varchar(3) NULL CONSTRAINT (CHECK (VALUE
'Yes', 'No')),
Prior_Rx varchar(3) NULL CONSTRAINT (CHECK ('Yes',
'No')),
Prior_Rx varchar(3) NULL CONSTRAINT (CHECK (VALUE Yes,
No)),
Prior_Rx varchar(3) NULL CONSTRAINT (CHECK (Yes, No)),

Prior_Rx varchar(3) NULL CONSTRAINT (CHECK (Yes OR
No)),
Prior_Rx varchar(3) NULL CHECK (Yes OR No),
Prior_Rx varchar(3) NULL CHECK ('Yes' OR 'No'),

Obviously they were all wrong, could someone advise?
Thanks

-- Joe --

PS--------------------------------------------------------------------------
-------------