CHECK Constraint

[Hari Om]

Hi ORAPERTS (ORAcle xPERTS)

How can I define a CHECK CONSTRAINT on a table for a field like zip
code....?

Here is what I am doing:
create table test
(
state varchar2(5) check (state
in('AL','AK','A Z','AR','CA','C O','CT','DE'))

)

but it seems that it freaks out....wonder why...? I tried using DOUBLE
QUOTES also but in vain....what is a correct way?

THANKS!

[Guido Konsolke]

Re: CHECK Constraint

"Hari Om" <hari_om@hotmai l.comwrote in
news:d1d5ebe4.0 309051319.33b3c 3b2@posting.goo gle.com...

Hi ORAPERTS (ORAcle xPERTS)
>
How can I define a CHECK CONSTRAINT on a table for a field like zip
code....?
>
Here is what I am doing:
create table test
(
state varchar2(5) check (state
in('AL','AK','A Z','AR','CA','C O','CT','DE'))
>
)
>
but it seems that it freaks out....wonder why...? I tried using

DOUBLE

QUOTES also but in vain....what is a correct way?
>
THANKS!

Hi Hari Om,

your example works for me. What do you mean
with 'seems to freak out'? By the way: I prefer
the 'alter table... add constraint...' statement.
In this I can easily define descriptive names
for them.

hth,
Guido

[Stephen_CA]

Re: CHECK Constraint

hari_om@hotmail .com (Hari Om) wrote in message news:<d1d5ebe4. 0309051319.33b3 c3b2@posting.go ogle.com>...

Hi ORAPERTS (ORAcle xPERTS)
>
How can I define a CHECK CONSTRAINT on a table for a field like zip
code....?
>
Here is what I am doing:
create table test
(
state varchar2(5) check (state
in('AL','AK','A Z','AR','CA','C O','CT','DE'))
>
)
>
but it seems that it freaks out....wonder why...? I tried using DOUBLE
QUOTES also but in vain....what is a correct way?
>
THANKS!

Just watch your syntax :

This will work

create table test( state varchar2(5) constraint test_chk CHECK(STATE
IN('AL','AK','A Z','AR','CA','C O','CT','DE'))) ;
Table created.

You can test it by inserting an invalid value:

SQLINSERT INTO TEST VALUES('YY');
INSERT INTO TEST VALUES('YY')
*
ERROR at line 1:
ORA-02290: check constraint (STEVE.TEST_CHK ) violated


I hope this helps,

Steve

[Mike Sherrill]

Re: CHECK Constraint

On 5 Sep 2003 14:19:59 -0700, hari_om@hotmail .com (Hari Om) wrote:

>How can I define a CHECK CONSTRAINT on a table for a field like zip
>code....?
>
>Here is what I am doing:
>create table test
>(
>state varchar2(5) check (state
>in('AL','AK',' AZ','AR','CA',' CO','CT','DE'))
>
>)

FWIW, those aren't ZIP codes. Consider putting all your postal codes
into a table and set a foreign key reference to it.

--
Mike Sherrill
Information Management Systems