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?
"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 2005Received on Mon Aug 15 2005 - 05:07:23 CDT
![]() |
![]() |