Re: Can Check Constraints be Reused for Multiple Columns?

<fitzjarrell_at_cox.net> wrote in message
news:1123534944.471198.287170_at_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 Received on Mon Aug 08 2005 - 16:36:31 CDT