Re: function in check constraint???

Thomas Kyte wrote:

> >Is it possible to use/call a function in a check constraint???
> >
>
> no it is not.

Bummer. I had secondary reasons for wanting to use a function.

I was hoping to define data validation "functions" could be used in constraints AND called to validate data during input (especially Java/JFC data input). If I can't call functions from contraints I have to either use all triggers or define the "constraint" two (or more) times.

> a BEFORE INSERT OR UPDATE, FOR EACH ROW trigger could do this, however looking
> at your logic, it would be most efficient to simply:
>
> SQL> l
> 1 create table t ( x varchar2(255)
> 2 check ( x is null or
> 3 translate( x, 'xcdops', 'yxxxxx' ) = rpad( 'x', length(x), 'x' ) )
> 4* )

Neat trick. Thanks.

I used the following variation. Would my variation be more or less efficient?

create table t (x varchar2(255)
check (

    x is null
    or
    length( rtrim( translate( x, 'cdops', ' '), ' ') ) = 0 );

Thanks again.
R.Parr Received on Fri Mar 05 1999 - 12:11:07 CST