Re: Can Check Constraints be Reused for Multiple Columns?

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message news:42F8B8C4.51B0_at_yahoo.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?
>
> alter table T
> add constraint C check
> ( col1 in ('Y','N')
> and col2 in ('Y','N')
> and col3 in ('Y','N')
> and col4 in ('Y','N')
> and col5 in ('Y','N')
> )
>
> Using a trigger will have significantly more overhead than a constraint,
> so I'm not sure why others are suggesting it
>
> hth
> connor
>
> --
> Connor McDonald
> Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
> Co-author: "Oracle Insight - Tales of the OakTable"
>
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: connor_mcdonald_at_yahoo.com
>
>
> "Semper in excremento, sole profundum qui variat."
>
> ------------------------------------------------------------

A couple of extra points to consider:

    colX in ('Y','N')
doesn't stop colX from being null, so
the O/P may want to include NOT NULL
declarations on the column, or check not nulls in the constraint.

More importantly, though, there seem to be cases where Oracle can use a column-level constraint to generate extra run-time predicates - but cannot do the same with table-level constraints. So I would be strongly inclined to keep column constraints at the column level, and only use table-level constraints when there was no alternative.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005