| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Can Check Constraints be Reused for Multiple Columns?
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');
Trigger created.
SQL>
SQL> insert into bunchacols(yesno) values ('Z');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from bunchacols;
Y N Y N W W B Y H
Z
That trigger attempt didn't work, so recode and try again:
SQL>
SQL> truncate table bunchacols;
Table truncated.
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 then
10 raise notvalid;
11 end if;
12 if nvl(:new.noyes,'Y') not in ('Y','N')
13 then
14 raise notvalid;
15 end if;
16 if nvl(:new.yes_no,'Y') not in ('Y','N')
17 then
18 raise notvalid;
19 end if;
20 if nvl(:new.no_yes,'Y') not in ('Y','N')
21 then
22 raise notvalid;
23 end if;
24 if nvl(:new.why,'Y') not in ('Y','N')
25 then
26 raise notvalid;
27 end if;
28 if nvl(:new.whynot,'Y') not in ('Y','N')
29 then
30 raise notvalid;
31 end if;
32 if nvl(:new.byallmeans,'Y') not in ('Y','N')
33 then
34 raise notvalid;
35 end if;
36 if nvl(:new.youdontsay,'Y') not in ('Y','N')
37 then
38 raise notvalid;
39 end if;
40 if nvl(:new.hootenany,'Y') not in ('Y','N')
41 then
42 raise notvalid;
43 end if;
44 exception
45 when notvalid then
46 raise_application_error(-20998,'Invalid character for
column');
Trigger created.
SQL>
SQL> insert into bunchacols(yesno) values ('Z');
insert into bunchacols(yesno) values ('Z')
*
ERROR at line 1:
ORA-20998: Invalid character for column ORA-06512: at "SCOTT.BUNCHACOLS_CK", line 43 ORA-04088: error during execution of trigger 'SCOTT.BUNCHACOLS_CK'
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from bunchacols;
no rows selected
SQL> The last trigger works for any specified column, so you COULD use a trigger to avoid writing X number of check constraints, however you'll need to write X number of if statements to get it to work properly. I really don't know which is more work, adding the checn constraints or writing the trigger.
My two cents.
David Fitzjarrell Received on Mon Aug 08 2005 - 16:02:24 CDT
 |
 |