Re: Can Check Constraints be Reused for Multiple Columns?

> >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?
>
> there is not
> >
> >Or is there a better way of doing this?
>
> You could of course set up a trigger, combining a bunch of
>
> if not (:new.column_name in ('Y','N')) then
> raise_application_error(<some negative number greater than
> -20000>,<some descriptive text>);
> end if;

I guess this is where so-called "domains" would make a great replacement :-)

CREATE DOMAIN yesno CHAR(1) CHECK (Value in ('Y', 'N') );

CREATE TABLE mytable
(
 ID NUMBER NOT NULL PRIMARY KEY,
 IS_ACTIVE yesno,
 IS_DELETED yesno,
...
other stuff...
...
)

Pitty Oracle doesn't have these... :-(

-- 
With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com