Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newsgate.cistron.nl!skynet.be!skynet.be!newspost001!tjb!not-for-mail
Reply-To: "Matthias Hoys" <idmwarpzone_NOSPAM_@yahoo.com>
From: "Matthias Hoys" <idmwarpzone_NOSPAM_@yahoo.com>
Newsgroups: comp.databases.oracle.server
References: <1123525527.687653.327550@f14g2000cwb.googlegroups.com> <1123534944.471198.287170@g49g2000cwa.googlegroups.com>
Subject: Re: Can Check Constraints be Reused for Multiple Columns?
Date: Mon, 8 Aug 2005 23:36:31 +0200
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
Lines: 73
Message-ID: <42f7d05f$0$10960$ba620e4c@news.skynet.be>
Organization: -= Belgacom Usenet Service =-
NNTP-Posting-Host: 4ee89999.news.skynet.be
X-Trace: 1123536991 news.skynet.be 10960 81.243.134.179:2260
X-Complaints-To: usenet-abuse@skynet.be
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:249135


<fitzjarrell@cox.net> wrote in message 
news:1123534944.471198.287170@g49g2000cwa.googlegroups.com...
> GeoPappas wrote:
>> I have a bunch of columns that are a CHAR(1) and should only contain
>> "Y" (for Yes) and "N" (for No).
>>
>> Instead of creating a separate check constraint for each and every
>> column, is there a way to set up one check constraint and reuse it for
>> multiple columns?
>>
>> Or is there a better way of doing this?
>
>
> Define 'bunch', for starters.
>
> I suppose you could try using a trigger to validate such data on input:
>
> SQL> @/export/home/oracle/scott/scotttest
> SQL> set echo on
> SQL> create table bunchacols(
>  2  yesno   char(1),
>  3  noyes   char(1),
>  4  yes_no  char(1),
>  5  no_yes  char(1),
>  6  why     char(1),
>  7  whynot  char(1),
>  8  byallmeans      char(1),
>  9  youdontsay      char(1),
> 10  hootenany      char(1));
>
> Table created.
>
> SQL>
> SQL> create or replace trigger bunchacols_ck
>  2  before insert or update on bunchacols
>  3  for each row
>  4  declare
>  5       notvalid      exception;
>  6       pragma exception_init(notvalid, -20998);
>  7  begin
>  8       if nvl(:new.yesno,'Y') not in ('Y','N')
>  9       and nvl(:new.noyes,'Y') not in ('Y','N')
> 10       and nvl(:new.yes_no,'Y') not in ('Y','N')
> 11       and nvl(:new.no_yes,'Y') not in ('Y','N')
> 12       and nvl(:new.why,'Y') not in ('Y','N')
> 13       and nvl(:new.whynot,'Y') not in ('Y','N')
> 14       and nvl(:new.byallmeans,'Y') not in ('Y','N')
> 15       and nvl(:new.youdontsay,'Y') not in ('Y','N')
> 16       and nvl(:new.hootenany,'Y') not in ('Y','N')
> 17       then
> 18       raise notvalid;
> 19       end if;
> 20  exception
> 21       when notvalid then
> 22       raise_application_error(-20998,'Invalid character for
> column');
> 23  end;
> 24  /
>

Don't you have to replace the "and" with "or" ?

if nvl(:new.yesno,'Y') not in ('Y','N')
or nvl(:new.noyes,'Y') not in ('Y','N')
or nvl(:new.yes_no,'Y') not in ('Y','N')
...

The error raise should work fine now, no ?

Matthias